Доработаем наш недавний запрос про поиск более низкой цены на товар в другом магазине. Будем искать товар в другом магазине, с ценой хотя бы на 1500 дешевле:
SELECT pp.product_id,
pp.store_id,
pp.price,
ppl.store_id AS store_id_less,
ppl.price AS price_less
FROM product_price pp
JOIN product_price ppl
ON ppl.product_id = pp.product_id
AND ppl.store_id != pp.store_id
AND ppl.price < pp.price - 1500
ORDER BY pp.product_id,
pp.store_id,
ppl.store_id,
ppl.price
Запрос вернет всего 2 записи
product_id | store_id | price | store_id_less | price_less |
---|---|---|---|---|
1 | 301 | 12500.00 | 300 | 10500.00 |
4 | 500 | 22000.00 | 400 | 20000.00 |
Что делать, если хочется оставить в выборке товары, для которых не нашлось аналогичного более дешевого товара в другом магазине? На помощь приходят так называемые внешние соединения. Существуют следующие типы соединений:
INNER JOIN
или простоJOIN
- внутреннее соединение. В результате остаются только те строки, для которых нашлось соответствие. До сих пор мы использовали только этот тип соединений.LEFT JOIN
- левое внешнее соединение. Работает какJOIN
, но если для строки таблицы, находящейся по левую сторону ключевого словаLEFT JOIN
, не нашлось ни одной строки в таблице, находящейся по правую сторонуLEFT JOIN
, то строка все равно добавляется в результат, а значения столбцов правой таблицы равныnull
.RIGHT JOIN
- правое внешнее соединение. Работает какJOIN
, но если для строки таблицы, находящейся по правую сторону ключевого словаRIGHT JOIN
, не нашлось ни одной строки в таблице, находящейся по левую сторонуRIGHT JOIN
, то строка все равно добавляется в результат, а значения столбцов левой таблицы равныnull
.FULL JOIN
- полное внешнее соединение. Если для какой-либо из таблиц не нашлось строки в другой таблице, то строка все равно попадает в результат, а значения столбцов другой таблицы равныnull
.CROSS JOIN
- перекрестное (или декартово) произведение. Каждая строка одной таблицы соединяется с каждой строкой второй таблицы, давая тем самым в результате все возможные сочетания строк двух таблиц. Аналогичного результата можно достичь просто перечислив таблицы вFROM
через запятую.
Перепишем запрос с использованием LEFT JOIN
:
SELECT pp.product_id,
pp.store_id,
pp.price,
ppl.store_id AS store_id_less,
ppl.price AS price_less
FROM product_price pp
LEFT JOIN
product_price ppl
ON ppl.product_id = pp.product_id
AND ppl.store_id != pp.store_id
AND ppl.price < pp.price - 1500
ORDER BY pp.product_id,
pp.store_id,
ppl.store_id,
ppl.price
product_id | store_id | price | store_id_less | price_less |
---|---|---|---|---|
1 | 300 | 10500.00 | ||
1 | 301 | 12500.00 | 300 | 10500.00 |
1 | 800 | 12000.00 | ||
2 | 500 | 26100.00 | ||
... | ... | ... | ... | ... |