Sql join where: sql — Condition within JOIN or WHERE

Joins SQLite — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном пособии вы узнаете, как использовать SQLite JOINS (inner и outer) с синтаксисом и примерами.

Описание

SQLite JOINS используются для извлечения данных из нескольких таблиц. SQLite JOIN выполняется всякий раз, когда две или более таблицы объединяются в операторе SQL.
Существуют различные типы SQLite JOIN:

• INNER JOIN (или иногда называется простым соединением)
• LEFT OUTER JOIN (или иногда называется LEFT JOIN)
• CROSS JOIN

Подсказка. В SQLite не поддерживаются RIGHT OUTER JOIN и FULL OUTER JOIN.

Итак, давайте обсудим синтаксис SQLite JOIN, рассмотрим наглядные иллюстрации SQLite JOINS и рассмотрим некоторые примеры.

INNER JOIN (Простое соединение)

SQLite INNER JOIN это самый распространенный тип объединения. SQLite INNER JOINS возвращают все строки из нескольких таблиц, где выполняется условие соединения.

Синтаксис

Синтаксис INNER JOIN в SQLite:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Рисунок

На этом рисунке SQLite INNER JOIN возвращает затененную область:

SQLite INNER JOIN будет возвращать записи, где пересекаются table1 и table2.

Пример

Вот пример SQLite INNER JOIN:

SELECT employees.employee_id,
employees.last_name,
positions.title
FROM employees
INNER JOIN positions
ON employees.position_id = positions.position_id;

В этом SQLite примере INNER JOIN будут возвращены все строки из таблиц employees и positions, где в таблицах employees и positions есть совпадающее значение position_id.

Давайте посмотрим на некоторые данные, чтобы понянть, как работает INNER JOINS:

У нас есть таблица с именем employee и четырьмя полями (employee_id, last_name, first_name и position_id). Она содержит следующие данные:

У нас есть еще одна таблица с названием position с двумя полями (position_id и title). Она содержит следующие данные:

Если мы запустим SQLite оператор SELECT (который содержит INNER JOIN) ниже:

SELECT employees. employee_id,
employees.last_name,
positions.title
FROM employees
INNER JOIN positions
ON employees.position_id = positions.position_id;

Наш результирующий набор будет выглядеть так:

Строка для employee_id 10003 из таблицы employees была бы опущена, так как соответствующий position_id не существует в таблице positions. Строка для position_id, равная 4, из таблицы positions будет опущена, поскольку этот positions не существует в таблице employees.

Старый синтаксис

В заключение следует отметить, что приведенный выше пример SQLite INNER JOIN можно переписать с использованием более старого неявного синтаксиса следующим образом (но мы все же рекомендуем использовать синтаксис ключевого слова INNER JOIN):

SELECT employees.employee_id,
employees.last_name,
positions.title
FROM employees, positions
WHERE employees.position_id = positions.position_id;

LEFT OUTER JOIN

Другой тип объединения SQLite называется LEFT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с левосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны (выполняется условие объединения).

Синтаксис

Синтаксис SQLite LEFT OUTER JOIN:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

Рисунок

На этом рисунке SQLite LEFT OUTER JOIN возвращает затененную область:

SQLite LEFT OUTER JOIN возвращает все записи из table1 и только те записи из table2, которые пересекаются с table1.

Пример

Вот пример SQLite LEFT OUTER JOIN:

SELECT employees.employee_id,
employees.last_name,
positions.title
FROM employees
LEFT OUTER JOIN positions
ON employees.position_id = positions.position_id;

В этом примере LEFT OUTER JOIN будут возвращены все строки из таблицы employees и только те строки из таблицы positions, где объединенные поля равны.

Если значение position_id в таблице employees не существует в таблице positions, все поля в таблице positions будут отображаться как в результирующем наборе.

Давайте посмотрим на некоторые данные, чтобы понять, как работает LEFT OUTER JOINS:

У нас есть таблица с employee и четырьмя полями (employee_id, last_name, first_name и position_id). Она содержит следующие данные:

У нас есть вторая таблица с position и двумя полями (position_id и title). Она содержит следующие данные:

Если мы запустим оператор SELECT (который содержит LEFT OUTER JOIN) ниже:

SELECT employees. employee_id,
employees.last_name,
positions.title
FROM employees
LEFT OUTER JOIN positions
ON employees.position_id = positions.position_id;

Наш результирующий набор будет выглядеть так:

Строка для employee_id = 10003 будет включена, поскольку был использован LEFT OUTER JOIN. Однако вы заметите, что поле заголовка для этой записи содержит значение , поскольку в таблице positions нет соответствующей строки.

CROSS JOIN

Другой тип SQLite объединения называется CROSS JOIN. Этот тип объединения возвращает комбинированный результирующий набор с каждой строкой из первой таблицы, сопоставленной с каждой строкой из второй таблицы. Это также называется декартовым произведением.

Синтаксис

Синтаксис для SQLite CROSS JOIN:

SELECT columns
FROM table1
CROSS JOIN table2;

Подсказка. В отличие от соединения INNER или OUTER, CROSS JOIN не имеет условий для объединения двух таблиц.

Рисунок

На этом рисунке SQLite CROSS JOIN возвращает каждую строку из table1, соответствующую каждой строке из table2.

Пример

Вот пример SQLite CROSS JOIN:

SELECT *
FROM positions
CROSS JOIN departments;

Этот пример CROSS JOIN вернул бы все строки из таблицы departments, соответствующие всем строкам из таблицы positions.

Давайте рассмотрим некоторые данные, чтобы объяснить, как работает CROSS JOINS:

У нас есть таблица positions содержащая следующие данные:

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

Если мы запустим запрос SELECT (который содержит CROSS JOIN) ниже:

SELECT *
FROM positions
CROSS JOIN departments;

Наш результирующий набор будет выглядеть так:

Поскольку таблица positions имеет 4 строки, а departments — 2 строки, перекрестное соединение вернет 8 строк (потому что 4×2 = 8). Каждая строка таблицы positions сопоставляется с каждой строкой таблицы departments.

sql — Условие в рамках JOIN или WHERE

Есть ли разница (производительность, передовой опыт и т. д.) между помещением условия в предложение JOIN и предложение WHERE?

Например…

 -- Условие в JOIN
ВЫБИРАТЬ *
ОТ dbo.Клиенты КАК CUS
ВНУТРЕННЕЕ СОЕДИНЕНИЕ dbo. Заказы КАК ЗАКАЗ
ON CUS.CustomerID = ORD.CustomerID
И CUS.FirstName = 'Джон'
-- Условие в WHERE
ВЫБИРАТЬ *
ОТ dbo.Клиенты КАК CUS
ВНУТРЕННЕЕ СОЕДИНЕНИЕ dbo. Заказы КАК ЗАКАЗ
ON CUS.CustomerID = ORD.CustomerID
ГДЕ CUS.FirstName = 'Джон'
 

Что вы предпочитаете (и, возможно, почему)?

• sql
• производительность

5

Реляционная алгебра допускает взаимозаменяемость предикатов в предложении WHERE и INNER JOIN , поэтому даже запросы INNER JOIN с предложениями WHERE могут иметь предикаты, переупорядоченные оптимизатором так, чтобы они 0027 уже может быть исключено в течение ПРИСОЕДИНЯЙТЕСЬ к процессу .

Я рекомендую вам писать запросы как можно более читабельно.

Иногда это включает в себя создание относительно "неполного" INNER JOIN и помещение некоторых критериев в WHERE просто для того, чтобы упростить обслуживание списков критериев фильтрации.

Например, вместо:

 ВЫБЕРИТЕ *
ОТ клиентов c
INNER JOIN CustomerAccounts ок.
    ON ca.CustomerID = c.CustomerID
    И c.State = 'Нью-Йорк'
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
    ON ca.AccountID = a.AccountID
    И а.Статус = 1
 

Запись:

 ВЫБЕРИТЕ *
ОТ клиентов c
INNER JOIN CustomerAccounts ок.
    ON ca.CustomerID = c.CustomerID
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
    ON ca.AccountID = a.AccountID
ГДЕ c.State = 'Нью-Йорк'
    И а.Статус = 1
 

Но это зависит, конечно.

14

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

Однако то, где вы ставите условие, имеет огромное значение, если вы используете левое или правое соединение. Например, рассмотрим эти два запроса:

 SELECT *
ОТ dbo.Клиенты КАК CUS
LEFT JOIN dbo.Заказы КАК ЗАКАЗ
ON CUS.CustomerID = ORD.CustomerID
ГДЕ ORD.OrderDate > '20090515'
ВЫБИРАТЬ *
ОТ dbo.Клиенты КАК CUS
LEFT JOIN dbo.Заказы КАК ЗАКАЗ
ON CUS.CustomerID = ORD.CustomerID
И ORD.OrderDate>'20090515'
 

Первый выдаст вам только те записи, которые имеют заказ от 15 мая 2009 г.тем самым преобразуя левое соединение во внутреннее соединение.

Второй даст эти записи плюс любые клиенты без заказов. Набор результатов сильно различается в зависимости от того, где вы ставите условие. (Выберите * только для примера, конечно, вы не должны использовать это в производственном коде.)

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

 ВЫБЕРИТЕ *
ОТ dbo.Клиенты КАК CUS
LEFT JOIN dbo.Заказы КАК ЗАКАЗ
ON CUS.CustomerID = ORD.CustomerID
ГДЕ ORD.OrderID имеет значение null
 

5

Большинство продуктов РСУБД одинаково оптимизируют оба запроса. В статье Питера Гулутцана и Труди Пельцер «Настройка производительности SQL» они протестировали РСУБД нескольких брендов и не обнаружили разницы в производительности.

Я предпочитаю отделять условия соединения от условий ограничения запроса.

Если вы используете OUTER JOIN , иногда необходимо указать условия в предложении соединения.

2

WHERE будет фильтровать после выполнения JOIN.

Фильтр для JOIN, чтобы предотвратить добавление строк во время процесса JOIN.

3

Я предпочитаю JOIN для объединения полных таблиц/представлений, а затем использую WHERE, чтобы ввести предикат результирующего набора.

Синтаксически чище.

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

Меня всегда слегка забавляет, когда кто-то показывает свои результаты бенчмаркинга SQL и выполняет обе версии sproc 50 000 раз в полночь на сервере разработки и сравнивает среднее время.

Согласитесь со вторым по количеству голосов ответом, что это будет иметь большое значение при использовании LEFT JOIN или RIGHT JOIN . На самом деле два приведенных ниже утверждения эквивалентны. Таким образом, вы можете видеть, что предложение AND выполняет фильтр перед JOIN , в то время как WHERE 9Предложение 0020 выполняет фильтрацию после JOIN .

 ВЫБЕРИТЕ *
ОТ dbo.Клиенты КАК CUS
LEFT JOIN dbo.Заказы КАК ЗАКАЗ
ON CUS.CustomerID = ORD.CustomerID
И ORD.OrderDate>'20090515'
ВЫБИРАТЬ *
ОТ dbo.Клиенты КАК CUS
LEFT JOIN (SELECT * FROM dbo.Orders WHERE OrderDate>'20090515') AS ORD
ON CUS.CustomerID = ORD.CustomerID
 

1

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

Помещение условия в соединение кажется мне "семантически неправильным", поскольку JOIN не для этого. Но это очень качественно.

Дополнительная проблема: если вы решите переключиться с внутреннего соединения, скажем, на правое соединение, наличие условия внутри JOIN может привести к неожиданным результатам.

1

Лучше добавить условие в Join. Производительность важнее удобочитаемости. Для больших наборов данных это важно.

2

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя электронную почту и пароль

Опубликовать как гость

Электронная почта

Требуется, но никогда не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания и подтверждаете, что прочитали и поняли нашу политику конфиденциальности и кодекс поведения.

Как вы присоединяетесь к таблицам SQL?. ГДЕ или ON предложение? | by Felix Otoo

ГДЕ или ON предложение?

Опубликовано в

·

Чтение: 6 мин.

·

12.09.2021

Фото Алины Грубняк на Unsplash

Сразу, не пользуюсь и не буду пользоваться 9 0019 Предложение WHERE для объединения таблиц .

Эта статья является попыткой убедить вас сделать то же самое, если вы используете ГДЕ пункт. Вместо этого используйте предложение ON для объединения таблиц.

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

Вы спрашиваете меня, почему?

Я же говорил вам, что я не сторонник предложения WHERE , используемого для соединения таблиц. Я использую его только для фильтрации данных. Это плохо меня раздражает (съежая), когда используется для соединения таблиц. Мой разум работает с перегрузкой, особенно если используется для достижения нескольких объединений за пределами двух таблиц.

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

Пример SQL-запроса, соединяющего несколько таблиц с помощью предложения WHERE. Этот SQL использует то же предложение WHERE для фильтрации.

См. приведенный выше SQL-запрос, объединяющий 6 таблиц с помощью предложения WHERE . Что еще хуже, он также выполняет фильтрацию данных с использованием 3 .И логических операторов с тем же предложением WHERE .

Переписывание с использованием предложения ON для достижения тех же результатов выглядит примерно так. Награда очень сильно заметна сразу.

Во-первых, беспорядок в конце запроса, где мы использовали предложение WHERE , был аккуратно убран. Теперь очевидно, что делает предложение WHERE — одно — только фильтрация строк.

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

Переписанный текст также выглядит целостным.

1. Фильтрация данных

Предложение WHERE превосходно и больше подходит для фильтрации данных, а не для объединения таблиц.

2. Когнитивная перегрузка

Использование предложения WHERE для фильтрации данных и объединения таблиц создает когнитивную перегрузку. Предложение WHERE влияет на удобочитаемость и понимание запросов при одновременном использовании для объединения таблиц и фильтрации данных.

3. Limited

Предложение WHERE ограничивает вас в различных достижимых объединениях таблиц. Предложение WHERE достигает только INNER JOIN , которое является одним из 5 типов соединений, которые вы можете выполнить с помощью предложения ON .

4. Отсутствующие записи

Поскольку выполнение объединения таблиц с предложением WHERE технически ведет себя так же, как INNER JOIN , записи в объединенных таблицах должны соответствовать условию, выраженному в Предложение WHERE должно быть частью результата объединенной таблицы. Несоблюдение этих фундаментальных знаний может привести к отсутствию важных записей в результатах вашего запроса. Этот надзор может быть дорогостоящим в финансовом приложении, таком как банковское приложение и приложение для денежных переводов.

Как я указывал ранее, использование предложения WHERE дает те же результаты, что и использование предложения ON вместе с предложением INNER JOIN .

Помимо возможности достижения INNER JOIN с помощью ON , вы также можете использовать другие формы соединений таблиц SQL. Преимущества этих других типов соединений таблиц — достаточные аргументы, чтобы оставить предложения WHERE строго для фильтрации данных. Это также обеспечивает согласованность ваших запросов и уменьшает головную боль при просмотре и обслуживании SQL для других разработчиков, поскольку им не нужно изо всех сил пытаться различать условия, которые используются для соединений таблиц и для фильтрации данных.

В оставшейся части этой статьи я покажу на примере данных три другие формы соединений SQL, помимо внутреннего соединения.

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

Создание таблиц, person и contact

SQL-запрос для создания таблицы «person» SQL-запрос для создания таблицы «contact»

Загрузите образцы данных person.csv и contact.csv и заполните таблицы person и контакт соответственно.

Разархивируйте и, наконец, заполните таблицы образцами данных, используя приведенные ниже запросы. Не забудьте заменить /path/to/person.csv и /path/to/contact. csv с путем к вашим загруженным разархивированным копиям.

Если вы все сделали правильно, оператор select для таблиц должен выдать следующие результаты:

Table person

 SELECT * FROM person; 

person.csv

Таблица контактов

 SELECT * FROM contact; 

contact.csv

Помимо INNER JOIN , существует 4 других типа соединений: FULL OUTER JOIN , LEFT OUTER JOIN , ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ и ПОПЕРЕЧНОЕ СОЕДИНЕНИЕ .

В этой статье мы поговорим о LEFT OUTER JOIN , RIGHT OUTER JOIN и FULL OUTER JOIN

LEFT OUTER JOIN

Левое внешнее соединение объединяет две таблицы, перечисляя указанные столбцы из левой таблицы. и присоединение к ним в столбцы из правой таблицы, если обе таблицы удовлетворяют условию, указанному в предложении ON .

Значение NULL устанавливается для столбцов из правой таблицы, когда условие не соответствует столбцу из левой таблицы.

В приведенном ниже примере таблица человек находится в левом внешнем соединении с контактом таблицы .

Стол человек левый стол и контакт правый стол.

Вы замечаете, что столбцы имя , пол , возраст из левой таблицы человек присоединяются к столбцу phone_number из правой таблицы контакт . Также очевидно, что последние 4 строки без совпадающих person_id имеет номер phone_number , установленный в NULL.

 SELECT person.name, 
 person.gender, 
 person.age, 
 person.phone_number 
 FROM person 
 LEFT OUTER JOIN contact 
 ON person.id = contact.person_id 

Выход:

Выход LEFT OUTER ПРИСОЕДИНЯЙТЕСЬ между таблицами person и contact

RIGHT OUTER JOIN

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

Значение NULL устанавливается для столбцов из левой таблицы, когда условие не соответствует столбцу из левой таблицы.

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

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

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

Список имя , пол , 9Таблица 0019 age from person соединяется с phone_number из таблицы contact , если они оба соответствуют условию, указанному в пункте ON .

 ВЫБОР человека.имя, 
 контакт.мобильный_номер, 
 контакт.домашний_адрес 
 ОТ человека 
 ПРАВО ВНЕШНЕЕ СОЕДИНЕНИЕ контакт 
 ВКЛ человек.ид = контакт.person_id 

Выход:

Выход ПРАВА ВНЕШНЕЕ СОЕДИНЕНИЕ между таблицами человек и контакт

ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ

Полное внешнее соединение объединяет две таблицы путем перечисления столбцов из обеих таблиц. Значение NULL устанавливается для столбцов левой или правой таблицы, если условие не выполняется.

В приведенном ниже примере таблица человек и контакт объединяются вместе более полным внешним соединением ON при условии, что столбец id в person соответствует столбцу person_id в контакт стол. Строки из каждой таблицы, которые не соответствуют этому условию, по-прежнему отображаются в результатах, в столбцах которых установлено значение 9.