В этом практическом уроке мы узнаем о различных вариантах объединения данных из двух (или более) таблиц в запросе SELECT
при работе с MySQL, а именно: INNER JOIN, LEFT JOIN, RIGHT JOIN, SELF JOIN и CROSS JOIN.
Слово «Join» переводится как «присоединять, связывать» и т.д. Именно такую задачу этот оператор и выполняет при работе с базами данных MySQL: берет данные из нескольких таблиц и объединяет в один исходный результат.
Присоединение осуществляется с помощью общих для нескольких таблиц столбцов. Эти столбцы называются столбцами внешнего ключа (Foreign Key). Присоединение полезно, когда информация разбросана по разным таблицам и ее нужно собрать вместе. Например, у вас есть таблица users
с данными клиентов, а в другой таблице orders
вы храните данные по заказам клиентов. Объединив эти две таблицы, вы будете видеть более широкую картину: и данные клиента, и детали заказов этого клиента в одном месте.
Объединение с помощью оператора JOIN
— это метод связывания данных между одной (самообъединение – self-join) или несколькими таблицами на основе значений общего столбца между таблицами. Это позволяет получать данные за один SQL-запрос без создания временных таблиц.
Используя JOIN, можно объединять как две таблицы, так и гораздо больше.
Оператор присоединения используется в операторе SELECT
, и следует после запроса FROM
.
После ключевого слова ON
указывается условие присоединения. Это условие указывает, как две (или более) таблицы будут сравниваться.
Если объединять таблицы через запятую, тогда нельзя использовать конструкции ON
и USING
, такое условие может быть задано только в конструкции WHERE
.
В MySQL используются следующие типы подключений:
INNER JOIN
)LEFT JOIN
)RIGHT JOIN
)CROSS JOIN
)
Например, рассмотрим вариант, когда у вас есть 2 таблицы: users
и orders
. Общими для двух таблиц являются столбцы user_id
. Ниже рассмотрим примеры использования различных типов присоединения.
INNER JOIN
сопоставляет каждую строку в одной таблице с каждой строкой в других таблицах и позволяет извлекать строки, содержащие схожие столбцы обеих таблиц. Если значения обеих строк удовлетворяют условию объединения, INNER JOIN
создает новую строку, столбец которой содержит все столбцы из обеих таблиц. Иными словами, результатом будут только соответствующие строчки из обеих таблиц.
Если в условии объединения используется оператор равенства (=
), а названия столбцов в обеих таблицах, используемых для сопоставления, одинаковы, вместо ON
можно использовать условие USING
. Вот базовый синтаксис внутреннего JOIN, объединяющий наши две таблицы:
SELECT order_id FROM orders INNER JOIN users ON {условие};
Детали запроса:
SELECT
(в данном случае order_id
– номер заказа)FROM
(orders
).INNER JOIN
(users
).ON
. Условие присоединения определяет правило соответствия строк между основной таблицей и другой таблицей (поиск общих данных). Это условие известно как предикат объединения. В данном примере мы используем общее поле user_id
– идентификатор клиента.
Поскольку обе таблицы используют тот же столбец (user_id
), вы можете использовать конструкцию USING
вместо ON
:
SELECT * FROM orders INNER JOIN users USING(user_id);
Результатом будет общая таблица с данными из двух таблиц. Что-то типа этого:
+---------+----------+-----------+-------------+ | user_id | order_id | firstname | lastname | +---------+----------+-----------+-------------+ | 2 | 1 | Petr | Shevchenko | | 1 | 2 | Ivan | Goncharenko | | 2 | 3 | Petr | Shevchenko | | 3 | 4 | Stepan | Melnik | +---------+----------+-----------+-------------+ 4 rows in set (0.00 sec)
LEFT JOIN
позволяет запрашивать данные из двух или более таблиц.
Подобно внутреннему объединению, для левого присоединения также требуется предикат соединения (условие). При объединении двух таблиц с помощью LEFT JOIN
используется понятие левой и правой таблицы. В данном примере users
— левая (первая) таблица, а orders
— правая таблица. При LEFT JOIN
данные отбираются, начиная с левой таблицы.
Каждая строка левой таблицы сравнивается с каждой строкой правой таблицы.
Если значения в двух строках удовлетворяют условию объединения, LEFT JOIN
создает новую строку, столбцы которой содержат все столбцы строк обеих таблиц, и включает ее в набор результатов.
Если значения в двух строках не совпадают, LEFT JOIN
все равно создает новую строку, столбцы которой содержат столбцы строки в левой таблице и NULL
для столбцов строки в правой таблице.
Другими словами, присоединение слева выбирает все данные из левой таблицы независимо от того, есть соответствующие строки в правой таблице или нет. Если соответствующие строки из правой таблицы не найдены, присоединение слева использует NULL
для столбцов строки из правой таблицы в наборе результатов.
Вот более сложный синтаксис с использованием LEFT JOIN, объединяющий две таблицы:
SELECT orders.order_id, orders.created_at, users.lastname FROM users LEFT JOIN orders ON {условие} WHERE {условие};
Детали запроса:
orders.order_id
«USING
, если столбец, используемый для сопоставления в обеих таблицах, одинаковLEFT JOIN
очень полезен, если вы хотите найти строки в таблице, не имеющие соответствующей строки в другой таблицеON
мы можем использовать более сложные условия, например для фильтрации и/или сортировки результатов
Чтобы сократить код запроса, мы можем предыдущий пример записать с использованием псевдонимов (alias) таблиц, например:
SELECT o.order_id, o.created_at, u.lastname FROM orders AS o LEFT JOIN users AS u ON o.user_id = u.user_id WHERE u.firstname = 'Ivan';
Запись присоединения справа подобна записи левого присоединения, за исключением того, что обработка таблиц обратная – сначала данные выбираются из правой таблицы и сравниваются с левой таблицей. Если строка правой таблицы не имеет соответствующих строк в левой таблице, столбец левой таблицы будет иметь значение NULL
в конечном наборе результатов.
Вот пример запроса с использованием RIGHT JOIN, объединяющий наши две таблицы:
SELECT o.order_id, o.created_at, u.lastname FROM users AS u RIGHT JOIN orders AS o ON o.user_id = u.user_id;
Детали запроса:
users
— это левая таблица, а orders
— правая таблицаRIGHT JOIN
и LEFT JOIN
функционально эквивалентны, и они могут заменять друг друга, если изменять порядок таблиц{условие}
использует оператор равенства (=
), а объединенные столбцы обеих таблиц имеют одинаковые названия, вы также можете использовать синтаксис USING(название столбца)
вместо ON
RIGHT OUTER JOIN
является синонимом RIGHT JOIN
. Поэтому вы можете использовать их как взаимозаменяемыеWHERE
с оператором IS NULL
Оператор Self JOIN — это оператор MySQL, который объединяет или обрабатывает таблицу в базе данных с самой собой. По сути, функция JOIN
объединяет две или более таблиц в SQL, но здесь Self Join — это обычное объединение, позволяющее объединять записи в одной таблице на основе определенного условия.
Самообъединение позволяет подключить таблицу к самой себе. Поскольку MySQL не имеет специального синтаксиса самообъединения, вам нужно выполнять Self Join с помощью обычного объединения, такого как LEFT
или INNER JOIN
.
Поскольку вы ссылаетесь на ту же таблицу в одном запросе, вам нужно использовать псевдонимы таблицы, чтобы назначить таблице временное имя, когда вы ссылаетесь на нее во второй раз.
Чтобы выполнить самообъединение, выполните следующие действия:
Пример базового синтаксиса запроса Self Join в MySQL:
SELECT CONCAT(u.lastname, ' ', o.firstname) AS Customer, u.lastname, o.firstname FROM users u, users o WHERE u.user_id = o.user_id;
Детали запроса:
users
, которой присвоены два псевдонима u
и o
. Выражение WHERE
определяет условное выражение для фильтрации результатов.
Результатом предыдущего запроса будет что-то типа этого:
+-------------------+-------------+-----------+ | Customer | lastname | firstname | +-------------------+-------------+-----------+ | Goncharenko Ivan | Goncharenko | Ivan | | Shevchenko Petr | Shevchenko | Petr | +-------------------+-------------+-----------+ 2 rows in set (0.00 sec)
В отличие от внутреннего присоединения, левого и правого соединения, CROSS JOIN
не имеет условия присоединения.
Перекрестное соединение создает декартовое произведение строк из объединенных таблиц. Перекрестное соединение объединяет каждую строку первой таблицы с каждой строкой правой таблицы, чтобы создать общий набор результатов.
Предположим, что первая таблица имеет a
строк, а вторая таблица имеет b
строк. Тогда перекрестное объединение CROSS JOIN
вернет a * b
строк. Набор результатов будет включать все строки из обеих таблиц, где каждая строка представляет собой комбинацию каждой строки в первой таблице с каждой строкой во второй таблице.
Ниже показан синтаксис перекрестного соединения CROSS JOIN:
SELECT {название столбца(ов)} FROM {таблица 1} CROSS JOIN {таблица 2};
Детали запроса:
INNER JOIN
, LEFT JOIN
и RIGHT JOIN
, функция CROSS JOIN
не имеет предиката соединения. Другими словами, она не использует условие ON
или USING
.
Вот и все на сегодня!
В данном практическом уроке вы ознакомились с разными операторами присоединения JOIN
в MySQL, включая внутреннее присоединение (INNER JOIN
), присоединение слева (LEFT JOIN
), присоединение справа (RIGHT JOIN
), самоприсоединение (Self Join) и перекрестное присоединение (CROSS JOIN
), используемые для получения данных с одной, из двух или более таблиц. При использовании оператора JOIN
следует учитывать, что процесс объединения таблиц может быть ресурсоемким, поэтому следует присоединять только таблицы, данные из которых действительно необходимы. Чем больше таблиц вы присоединяете, тем больше будет снижаться производительность.
Спасибо за внимание! Надеемся, что данный урок принес вам практическую пользу.
Сегодняшний обзор посвящен людям, для которых важен вопрос конфиденциальности в Интернете и кто ценит свои…
Впервые о бренде Pro Plan услышали в 1986 году, когда он стал частью американской компании…
Страх наказания хуже самого наказания (В наказании есть нечто определенное, оно все же лучше, чем…
Если вы планируете разместить веб-сайт в Интернете, очень важно найти для него быстрый и надежный…
Учеба за границей уже давно ассоциируется с качественным образованием, новыми возможностями и множеством перспектив. Но…
Выбор мастера для ремонта и перетяжки мебели — задача, которая требует вдумчивого подхода. Ведь от…