Разработка

Для примеров используем следующие таблицы:

1. JOIN.

Полное объединение

Операторы JOIN, INNER JOIN и «,» (запятая) с указанием условий объединения дают пересечение для всех совпадающих значений из условия.

SELECT t1.*, t2.* FROM t1, t2 WHERE t1.i1 = t2.i2
#Аналоги:
SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1 = t2.i2
SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ON t1.i1 = t2.i2

В результате получим:

При этом если не указать для оператора «,» (запятая) условие выборки WHERE, то получим полное декартово произведение (пересечение каждой строки из первой таблицы с каждой строкой из второй):

(аналогичный результат получается при использовании CROSS JOIN)
INNER JOIN можно представить как декартово произведение таблиц, из которого потом отобрали только те строки, которые удовлетворяют условию ON, хотя на практике, конечно, используются более совершенные алгоритмы, такие как Hash join и Self-merge join, поскольку алгоритм декартова произведения очень неэффективен.

LEFT JOIN, RIGHT JOIN

При использовании оператора LEFT JOIN выбираются все значения из левой таблицы, после чего к ним подбираются соответствующие значения из правой, если таких нет, то все значения для правой таблицы в этой строке равны NULL.
RIGHT JOIN противоположен LEFT JOIN: всё абсолютно так же, но выбираются все значения для правой таблицы из выражения.

SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2

В результате получим:

SELECT t1.*, t2.* FROM t1, t2 ON t1.i1 = t2.i2
#другая запись
SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ON t1.i1 = t2.i2 WHERE
#аналогично по результату, хотя обычно медленнее
SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2 WHERE t2.i2 IS NOT NULL

При указании любого из операторов JOIN можно использовать ключевое слово USING() вместо ON, если названия столбцов в обеих таблицах одинаковы.

SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i = t2.i
#аналогично
SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 USING(i)

JOIN‘ы можно комбинировать:

SELECT t1.*, t2.*
FROM t1, t2
LEFT JOIN t3
ON t1.i = t3.i1 AND t2.i = t3.i2
WHERE t3.i2 IS NOT NULL

Self-join

Self-join объединяет таблицу саму с собой. Проще показать на примере.
Допустим есть таблица

И допустим, что нужно получить пары сотрудников, которые работают в одной стране. Результата можно добиться и уже описанными join’ами, но эффективнее будет следующий запрос:

SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F, Employee S
WHERE F.Country = S.Country
AND F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;

В результате получим:

В данном случае:
F и S - алиасы таблицы Employee
условие F.Country = S.Country исключает из результата пары, для которых страны не совпадают
условие F.EmployeeID < S.EmployeeID исключает пары с одинаковыми EmployeeID, а также дублирование ранее выведенных пар (т.е. сотрудник с EmployeeID=145 не выведется слева).

INNER JOIN в некоторых случаях значительно быстрее, чем OUTER JOIN (LEFT JOIN или RIGHT JOIN), поэтому лучше по возможности использовать INNER JOIN. Пример сравнения производительности с объяснением.
Виды JOIN'ов

2. Подзапросы.

Подзапросы организуются в виде вложенных SELECT'ов.
В принципе, в подзапросах можно использовать и другие операторы, но модифицирующие INSERT, REPLACE, DELETE, UPDATE при этом не могут модифицировать таблицу из основного запроса.

SELECT t1.*FROM t1 WHERE t1.i1 IN (SELECT i2 FROM t2 WHERE c2 = 'a')

Подзапрос может возвращать одно значение (скалярный подзапрос), несколько значений одного столбца, несколько значений нескольких столбцов (фактически, таблицу).
Со скалярным подзапросом можно использовать операторы сравнение ("=", ">", "<", "<>" и т.д.).
Приэтом важно, чтобы подзапрос возвращал только одно значение.
Это очень удобно в запросах типа

#так писать нельзя, т.к. значение MIN(i) неизвестно
#до того момента, как произойдёт выборка
#т.е. уже после того, как понадобится "WHERE"
SELECT * FROM t1 WHERE i = MIN(i)
#а вот так писать можно
SELECT * FROM t1 WHERE i = (SELECT MIN(i) FROM t1)

Если подзапрос возвращает одну строку, то можно использовать кортеж (набор значений для сравнения):
Это очень удобно в запросах типа

SELECT t1.* FROM t1
WHERE (i, c) = (SELECT i, c FROM t2 WHERE i = 2 LIMIT 1)

Если подзапрос может вернуть несколько значений, то лучше ипользовать операторы IN и NOT IN вместо = и <> соответственно.

SELECT t1.* FROM t1
WHERE (i, c) IN (SELECT i, c FROM t2)

Операторы EXISTS и NOT EXISTS нужны для того, чтобы определить, вернулись ли значения из подзапроса (используются для коррелированных подзапросов).

SELECT * FROM t1
WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE i > 10000)

Коррелированные подзапросы

содержат ссылки на значения внешнего запроса.

SELECT i FROM t1 WHERE j IN (SELECT k FROM t2 WHERE k = i)

Пример несколько странный получился.
Более реалистичный:

SELECT i FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.i1 = t1.i)

Подзапросы можно переписывать через операторы JOIN (в некоторых случаях может работать быстрее).

SELECT * FROM t1
WHERE i IN (SELECT i1 FROM t2 WHERE t2.i = 15)
#аналогично
SELECT t1.* FROM t1, t2
WHERE t1.i = t2.i1 AND t2.i = 15

* При этом могут возвращаться несколько разные результаты, если в t2.i1 содержатся неуникальные значения. В таком случае подзапрос выдаст только уникальные значения, а JOIN выдаст все. Чтобы этого избежать, нужно во втором запросе использовать оператор SELECT DISTINCT (выбрать только уникальные значения).

SELECT * FROM t1
WHERE i NOT IN (SELECT i1 FROM t2)
#аналогично
SELECT t1.*
FROM t1 LEFT JOIN t2 ON t2.i1 = t1.i
WHERE t2.i1 IS NULL

(при этом в подзапросе можно заметить отсутствие условия выборки;))

3. UNION

Выборка из нескольких таблиц последовательно.
UNION действует как SELECT DISTINCT, т.е. отбрасывает повторяющиеся значения. чтобы этого избежать, нужно использовать оператор UNION ALL

(SELECT i, c FROM t1 ORDER BY i DESC)
UNION
(SELECT i, d FROM t2 ORDER BY i)