Mysql Notes

• sql rdms joins relations snippet

RDMS Goals

  • Reduce redundancy
  • Efficient data retrieval
  • Intuitive data modification

Relations

  • Primary Key

    • Unique identifier of row
    • One per table
    • Does not allow NULL
    • Single or multiple columns (composite columns)

  • Unique Key

    • Ensures data is not duplicate
    • More than one per table
    • Allows one NULL

  • Foreign Key

    • Columns in a table that refer to a Primary Key of another table
    • Enforces referential integrity
    • One-to-one
    • One-to-many
    • Many-to-many

Data Retrieval

  • Selecting using mathematical functions
  • Distinct
  • DATE_FORMAT
  • AND, OR, NOT
  • LIMIT 15, 10; syntax
  • Update multiple

Joins

A SQL JOIN combines columns from two or more tables in a single result set.

You can use any operators for a JOIN.

CARTESIAN joins by default: http://www.databasejournal.com/features/mysql/article.php/3901221/Identifying-and-Eliminating-the-Dreaded-Cartesian-Product.htm

SELECT statements which contain a WHERE clause can easily hide a Cartesian Product because not all rows will appear in duplicate.

Don't use implicit inner join syntax

SELECT t1.*, t2.*
FROM Table1 t1, Table2 t2
WHERE t1.ID = t2.id

Use explicit syntax (SQL-92)

SELECT t1.*, t2.*
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID

Outer Join

  • Left Outer Join
  • Right Outer Join
  • Full Outer Join (MySql doesn't support this syntax)
  • Outer keyword is optional

Cross Join or Cartesian Join

The rows are multiplied. No join condition

Equi Join & Non-equi join

JOIN t2 ON t1.id = t2.t1_id

Natural Join & USING keyword

Don't use

Unions

  • Unions combine two or more SELECT statements into a single result set.

  • Each select must have the same number of columns

  • UNION removes duplicate rows. UNION ALL does not

  • can only use one order by

Subqueries

  • Nested query where the results of one query can be used in another query via a relational operator or aggregation function.

  • A subquery can have only one column in the SELECT clause if used in WHERE clause

  • ORDER BY not allowed

  • Subqueries can be used in WHERE, HAVING, FROM, and SELECT clauses.

Joins vs subqueries

  • Joins

    • can include columns from joining tables in the SELECT clause
    • easy to read and more intuitive

  • Subqueries

    • Can pass the aggregate values to the main query
    • can simplify long and complex queries

Correlated Subquery

  • Executed once per each row
  • Returns results based on the column of the main query