MySQL. Выборка значений из нескольких таблиц.

• 09, 09, 2009
•  admin
•  Databases
• Comments: 5

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

1. JOIN.

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

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

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

При этом если не указать для оператора «,» (запятая) условие выборки 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, 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, если названия столбцов в обеих таблицах одинаковы.

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

Self-join

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

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

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

В данном случае:
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 при этом не могут модифицировать таблицу из основного запроса.

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

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

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

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

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

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

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

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

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

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

3. UNION

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

Similar Posts

• Список полезных советов по оптимизации MySQL
• Триггеры в mySQL 5.0
• How to safely update data in MySQL
• Как узнать самые ненужные индексы в mysql
• PostgreSQL — генерирование повторяющихся значений для неупорядоченных данных