Уроки PHP - урок 3.6 - Работа с БД MySQL. Виды оператора JOIN.

В MySQL выборку с помощью JOIN можно производить разными способами. Мы постараемся рассмотреть все эти виды запросов. Вот список всех запросов с участием JOIN:

1. INNER JOIN
2. LEFT JOIN
3. LEFT JOIN без пересечений с правой таблицей
4. RIGHT JOIN
5. RIGHT JOIN без пересечений с левой таблицей
6. FULL OUTER
7. FULL OUTER где левая или правая таблица пустая

 

А вот иллюстрация к этим видам JOIN:

Я прикреплю к статье файлы нашего сайта, среди которых будет join.php в которых я буду выводить все записи с помощью разных операторов JOIN.

INNER JOIN

Начнем мы с этого оператора INNER JOIN, потому что этот оператор срабатывает по умолчанию, если вы пишите в запросе просто JOIN. Этот оператор выбирает все записи из двух таблиц, где выполняется условие идущее после оператора ON. У нас есть две таблицы Files и Messages:

Таблица Messages:

mid	bodytext	fid
1	Test	NULL
2	Hi	2
3	Hello	NULL

 Таблица Files:

fid	path
1	/files/1.png
2	/files/2.png
3	/files/3.png

Запрос с JOIN будет следующий:

SELECT * FROM Messages INNER JOIN Files ON Messages.fid=Files.fid

В результате будут выведены такие записи

mid	bodytext	fid	path
2	Hi	2	/files/2.png

То есть там где fid совпадает, то mysql выведит эти строки.

LEFT JOIN

При LEFT JOIN мы выводим все записи в которых в таблице слева (у нас это Messages),  в том числе и те записи в которых эти значения fid есть в таблице Files.

Таблица Messages:

mid	bodytext	fid
1	Test	2
2	Hi	NULL
3	Hello	3

 Таблица Files:

fid	path
1	/files/1.png
2	/files/2.png
3	/files/3.png

Запрос с LEFT JOIN будет следующий:

SELECT * FROM Messages LEFT JOIN Files ON Messages.fid=Files.fid

В результате будут выведены такие записи

mid	bodytext	fid	path
1	Test	2	/files/2.png
2	Hi	NULL	NULL
3	Hello	3	/files/3.png

LEFT JOIN будет нужен когда выводим все записи сообщений, а есть или нет прикрепленный файл, мы проверим уже через PHP.

LEFT JOIN без пересечений с правой таблицей

LEFT JOIN выводит все записи из левой таблицы, кроме тех в которых fid совпадают в правой таблице.

Таблица Messages:

 

mid	bodytext	fid
1	Test	2
2	Hi	NULL
3	Hello	3

 Таблица Files:

fid	path
1	/files/1.png
2	/files/2.png
3	/files/3.png

Запрос с LEFT JOIN без пересечений будет следующий:

SELECT * FROM Messages LEFT JOIN Files ON Messages.fid=Files.fid WHERE Files.fid IS NULL

В результате мы получим вот такую вот выборку:

mid	bodytext	fid	path
2	Hi	NULL	NULL

LEFT JOIN без перечений будет нужен когда выводим все записи без прикрепленных файлов.

RIGHT JOIN

RIGHT JOIN выводит все записи из правой таблицы, если есть пересечения, то выводится данные из левой таблицы.

Таблица Messages:

mid	bodytext	fid
1	Test	2
2	Hi	NULL
3	Hello	3

 Таблица Files:

fid	path
1	/files/1.png
2	/files/2.png
3	/files/3.png

Запрос с RIGHT JOIN будет следующий:

SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid=Files.fid

В результате мы получим вот такую вот выборку:

 

mid	bodytext	fid	path
NULL	NULL	1	/files/1.png
1	Test	2	/files/2.png
3	Hello	3	/files/3.png

RIGHT JOIN будет нужен когда выводим все прикрепленные файлы без разницы используются они или нет, просто все файлы.

RIGHT JOIN без пересечений

RIGHT JOIN без пересечений выводит все записи из правой таблицы, кроме тех где есть пересечения с левой таблицей.

Таблица Messages:

mid	bodytext	fid
1	Test	2
2	Hi	NULL
3	Hello	3

Таблица Files:

fid	path
1	/files/1.png
2	/files/2.png
3	/files/3.png

Запрос с RIGHT JOIN без пересечений будет следующий:

SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid=Files.fid WHERE Messages.fid IS NULL

Таким образом мы получим следующие данные:

mid	bodytext	fid	path
NULL	NULL	1	/files/1.png

RIGHT JOIN будет нужен когда выводим все прикрепленные файлы, которые не прикреплены ни к каким сообщениям. Например, если мы хотим вывести файлы которые не используются.

FULL OUTER JOIN

Несмотря на то что в языке SQL есть FULL OUTER JOIN, в MySQL этого оператора нет. Дело в том что подобный оператор это огромная нагрузка на сервер. Сейчас у нас 3 файла и 3 сообщения, при этом образуется 4 строк в результате выполнения запроса. Я не уверен, что это хорошая идея писать запрос, который дает в совокупности два запроса LEFT JOIN и RIGHT JOIN. Но все же есть возможность эмулировать запрос FULL OUTER JOIN.

Таблица Messages:

mid	bodytext	fid
1	Test	2
2	Hi	NULL
3	Hello	3

Таблица Files:

fid	path
1	/files/1.png
2	/files/2.png
3	/files/3.png

Эмуляция запроса с FULL OUTER JOIN будет следующей:

SELECT * FROM Messages LEFT JOIN Files ON Messages.fid = Files.fid UNION SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid = Files.fid

В этом запросе мы используем оператор UNION, чтобы объединить два запроса LEFT JOIN и RIGHT JOIN.

В результате мы получим следующие записи:

 

mid	bodytext	fid	path
1	Test	2	/files/2.png
2	Hi	NULL	NULL
3	Hello	3	/files/3.png
NULL	NULL	1	/files/1.png

И здесь я уже затрудняюсь сказать зачем потребуется FULL OUTER JOIN. Но раз это есть в SQL, то видимо потребуется потом.

FULL OUTER JOIN без пересечений

Еще один вид JOIN еще более безумный, чем просто FULL OUTER JOIN, а именно FULL OUTER JOIN без пересечений. Я даже не могу предложить где можно использовать этот вид JOIN. Потому что в результате мы получаем файлы которые не используются и сообщения без файлов. И как вы наверно уже догадались этого оператора тоже нет в MySQL. Остается его только эмулировать с помощью двух операторов LEFT JOIN без перечений и RIGHT JOIN без пересечений.

Эмуляция запроса FULL OUTER JOIN без пересечений:

$sql = 'SELECT * FROM Messages LEFT JOIN Files ON Messages.fid = Files.fid WHERE Files.fid IS NULL 
UNION 
SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid = Files.fid WHERE Messages.fid IS NULL';

В результате (исходные таблицы те же что и в примере с FULL OUTER JOIN) мы получим:

mid	bodytext	fid	path
2	Hi	NULL	NULL
NULL	NULL	1	/files/1.png

Вот наверно и все, в следующих уроках мы начнем писать еще более сложные запросы к нескольким таблицам сразу.