RDMS Goals

Relations

Data Retrieval

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


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


Subqueries

Joins vs subqueries

Correlated Subquery