Как запросить сразу несколько таблиц базы данных с помощью соединений SQL

Дядя Влад

Одно из самых больших преимуществ использования реляционных баз данных, таких как MySQL, заключается в том, что его реляционная структура позволяет легко хранить и запрашивать информацию в нескольких таблицах.

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

Инициализировать образец базы данных

Это не обязательно, но если вы хотите следовать примерам из этой статьи, вы можете инициализировать образец базы данных локально с помощью следующих команд терминала:

 git clone https://github.com/mdizak/sample-select-db.git
 cd sample-select-db
 sudo mysql < store.sql
 sudo mysql sampledb
 mysql> SELECT COUNT(*) FROM customers;

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

По умолчанию / ВНУТРЕННЕЕ соединение

Соединение по умолчанию, используемое в базах данных MySQL, называется ВНУТРЕННИМ соединением, и оно является наиболее распространенным и прямым. Это соединение возвращает все записи, для которых есть совпадающие записи в обеих таблицах, и отклоняет все остальные записи.

Например, если вы хотите видеть имя и фамилию клиента, а также сумму и дату заказа для всех заказов на сумму более 1000 долларов США, вы можете использовать следующий оператор SQL:

 
SELECT
 c.id, c.first_name, c.last_name, o.amount, o.created_at
 FROM
 customers c, orders o
 WHERE
 o.customer_id = c.id AND o.amount >= 1000;

Несколько примечаний относительно вышеуказанного запроса:

  • Выбираются пять разных столбцов: три из таблицы клиентов и два из таблицы заказов.
  • В предложении FROM определены две таблицы, но с суффиксами «c» и «o». Они просто определяют псевдонимы в SQL, могут быть чем угодно и используются для сокращения SQL-запроса.
  • O.customer_id = c.id является аспектом соединения запроса и обеспечивает правильную корреляцию между клиентами и заказами.

Другой и технически более синтаксически правильный способ написать тот же запрос представлен ниже:

 
SELECT
 c.id, c.first_name, c.last_name, o.amount, o.created_at
 FROM
 customers c INNER JOIN orders o
 ON
 customer_id = c.id
 WHERE
 o.amount >= 1000;

Вышеупомянутый запрос, как правило, немного легче читать, поскольку вы можете легко увидеть соединение между таблицами клиентов и заказов. Тем не менее, по сути, эти два запроса одинаковы и будут производить точно такие же записи.

LEFT Присоединяется

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

 
SELECT
 p.name, sum(item.amount) AS tamount
 FROM
 orders_items item LEFT JOIN products p
 ON
 item.product_id = p.id
 GROUP BY item.product_id ORDER BY tamount DESC

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

ВПРАВО присоединяется

Используя приведенный выше пример, обратите внимание на тот факт, что указанный выше запрос вернул только 19 записей, тогда как в базе данных всего 22 продукта. Это связано с тем, что запрос начинался с таблицы orders_items и оставался присоединенным к таблице продуктов, а поскольку некоторые продукты никогда не были заказаны, в таблице orders_items нет записей об этих продуктах.

Что произойдет, если вы хотите получить список всех продуктов с суммами продаж, включая продукты, которые не были заказаны? Попробуйте выполнить правильное соединение со следующим запросом:

 
SELECT
 p.name, sum(item.amount) AS tamount
 FROM
 orders_items item RIGHT JOIN products p
 ON
 item.product_id = p.id
 GROUP BY p.id ORDER BY tamount DESC

Это лучше, и теперь запрос возвращает полные 22 продукта, причем три из них имеют нулевое значение . Это связано с тем, что вместо использования orders_items в качестве основной таблицы, которая присоединяется к таблице продуктов, правое соединение переворачивает порядок и присоединяет таблицу продуктов к таблице orders_items.

Множественные соединения в запросе

Иногда вам необходимо объединить три или более таблиц вместе, чтобы получить определенный набор результатов.

Например, может быть, вам нужен список всех клиентов, которые приобрели микроволновую печь (идентификатор продукта № 1), включая их имена и дату заказа. Для этого требуется SELECT для трех таблиц, что может быть выполнено с помощью двух объединений со следующим запросом:

 
SELECT
 c.first_name, c.last_name, o.amount, o.created_at
 FROM
 customers c INNER JOIN orders o
 ON
 c.id = o.customer_id INNER JOIN orders_items item
 ON
 item.order_id = o.id
 WHERE
 item.product_id = 1 ORDER BY o.created_at;

Этот запрос возвращает все 426 заказов микроволн и работает должным образом. Сначала он сопоставляет всех клиентов с их соответствующими заказами, а затем выполняет дальнейшие запросы, в результате которых все заказы сопоставляются только с теми, которые находятся в таблице orders_items, которые содержат микроволновый продукт (идентификатор # 1).

Никогда не используйте подзапросы с предложениями IN

В качестве небольшого примечания: любой ценой вы всегда должны избегать использования подзапросов в своих SQL-запросах, таких как:

 SELECT first_name,last_name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE status = 'approved' AND amount < 100);

Запросы, подобные приведенным выше, очень неэффективны, используют большое количество ресурсов, и их следует по возможности избегать. Вместо этого используйте правильные соединения, как описано в предыдущих разделах. Например, приведенный выше запрос следует переписать как:

 SELECT c.first_name, c.last_name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.status = 'approved' AND o.amount < 100;

Экономьте время с помощью SQL-соединений

Мы надеемся, что эта статья поможет показать вам мощь реляционных баз данных, таких как MySQL, и то, как создавать SQL-запросы, которые извлекают записи из нескольких таблиц в одном запросе с помощью объединений, позволяя получать точные желаемые результаты.

Вы узнали о трех разных соединениях в SQL, как использовать псевдонимы для имен столбцов и таблиц, использовать несколько объединений в одном запросе и почему вам следует избегать подзапросов. Никогда больше не возитесь, пытаясь вручную скомпилировать разные наборы данных в один, и начните использовать объединения, чтобы произвести впечатление на своих коллег по работе и сэкономить время.