- Reduce redundancy
- Efficient data retrieval
- Intuitive data modification
- Unique identifier of row
- One per table
- Does not allow NULL
- Single or multiple columns (composite columns)
- Ensures data is not duplicate
- More than one per table
- Allows one NULL
- Columns in a table that refer to a Primary Key of another table
- Enforces referential integrity
- Selecting using mathematical functions
- AND, OR, NOT
- LIMIT 15, 10; syntax
- Update multiple
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
- 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
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
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
- can include columns from joining tables in the SELECT clause
- easy to read and more intuitive
- Can pass the aggregate values to the main query
- can simplify long and complex queries
- Executed once per each row
- Returns results based on the column of the main query