Различие между “и” и “где” в соединениях
Между чем различие
SELECT DISTINCT field1
FROM table1 cd
JOIN table2
ON cd.Company = table2.Name
and table2.Id IN (2728)
и
SELECT DISTINCT field1
FROM table1 cd
JOIN table2
ON cd.Company = table2.Name
where table2.Id IN (2728)
оба возвращают тот же результат, и у обоих есть то же, объясняет вывод
5 ответов
Во-первых, существует семантическая разница. Когда у вас есть объединение, вы говорите, что связь между двумя таблицами определяется этим условием. Так, в вашем первом примере вы говорите, что таблицы связаны по cd.Company = table2.Name AND table2.Id IN (2728). Когда вы используете предложение WHERE, вы говорите, что связь определяется cd.Company = table2.Name и что вам нужны только те строки, к которым применимо условие table2.Id IN (2728). Даже если они дают один и тот же ответ, для программиста, читающего ваш код, это означает совершенно разные вещи.
В этом случае предложение WHERE почти наверняка является тем, что вы имеете в виду, поэтому его следует использовать.
Во-вторых, есть разница в результате в том случае, если вы используете LEFT JOIN вместо INNER JOIN. Если вы включите второе условие как часть соединения, вы все равно получите строку результата, если условие не выполнится - вы получите значения из левой таблицы и нули для правой таблицы. Если вы включите условие как часть условия WHERE и это условие не сработает, вы не получите строку вообще.
Вот пример, демонстрирующий это.
Запрос 1 (WHERE):
SELECT DISTINCT field1
FROM table1 cd
LEFT JOIN table2
ON cd.Company = table2.Name
WHERE table2.Id IN (2728);
Результат:
field1
200
Запрос 2 (AND):
SELECT DISTINCT field1
FROM table1 cd
LEFT JOIN table2
ON cd.Company = table2.Name
AND table2.Id IN (2728);
Результат:
field1
100
200
Используемые тестовые данные:
CREATE TABLE table1 (Company NVARCHAR(100) NOT NULL, Field1 INT NOT NULL);
INSERT INTO table1 (Company, Field1) VALUES
('FooSoft', 100),
('BarSoft', 200);
CREATE TABLE table2 (Id INT NOT NULL, Name NVARCHAR(100) NOT NULL);
INSERT INTO table2 (Id, Name) VALUES
(2727, 'FooSoft'),
(2728, 'BarSoft');
- Соединение используется для отражения отношений между сущностями
- предложение where фильтрует результаты.
Таким образом, условия join являются "статическими" (если отношения между сущностями не меняются),
в то время как условия where являются специфическими для конкретного случая использования.
Нет никакой разницы, когда механизм оптимизации запроса разбивает его на соответствующие операторы запроса.
Разницы нет. "ON" - это как синоним "WHERE", поэтому второй вид выглядит так:
JOIN table2 WHERE cd.Company = table2.Name AND table2.Id IN (2728)
SQL происходит из реляционной алгебры .
Один из способов взглянуть на разницу заключается в том, что JOIN - это операции над наборами, которые могут производить больше записей или меньше записей в результате, чем у вас было в исходных таблицах. С другой стороны, WHERE всегда будет ограничивать количество результатов.
Остальная часть текста является дополнительным объяснением.
Обзор типов соединений снова см. В статье .
Когда я сказал, что условие where всегда будет ограничивать результаты, вы должны принять во внимание, что когда мы говорим о запросах к двум (или более) таблицам, вы должны каким-то образом объединять записи из этих таблиц, даже если нет Ключевое слово JOIN.
Таким образом, в SQL, если таблицы просто разделены запятой, вы фактически используете CROSS JOIN (декартово произведение), которое возвращает каждую строку из одной таблицы для каждой строки в другой.
И поскольку это максимальное количество комбинаций строк из двух таблиц, то результаты любого WHERE в перекрестно соединенных таблицах могут быть выражены как операция JOIN.
Но подождите, есть исключения из этого максимума, когда вы вводите соединения LEFT, RIGHT и FULL OUTER.
LEFT JOIN будет объединять записи из левой таблицы по заданному критерию с записями из правой таблицы, НО, если критерии объединения, просмотр строки из левой таблицы не удовлетворяется ни для каких записей в правой таблице, LEFT JOIN по-прежнему будет возвращать запись из левой таблицы, а в столбцах, которые будут исходить из правой таблицы, он будет возвращать NULL (RIGHT JOIN работает аналогично, но с другой стороны, FULL OUTER работает как и то, и другое одновременно).
Поскольку перекрестное соединение по умолчанию НЕ возвращает эти записи, вы не можете выразить эти критерии соединения с помощью условия WHERE, и вы вынуждены использовать синтаксис JOIN (исключение составляет oracle с расширением стандарта SQL и оператором =, но это не было принято ни другими поставщиками, ни стандартом).
Кроме того, соединения обычно, но не всегда, совпадают с существующей ссылочной целостностью и предполагают связи между сущностями, но я бы не стал придавать этому большое значение, поскольку условия where могут делать то же самое (за исключением упомянутого выше случая) и для хорошей СУБД не будет иметь значения, где вы укажете свои критерии.