Разница между "и" и "where" в соединении

В чем разница между

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)

оба возвращают один и тот же результат, и оба имеют одинаковый вывод

Ответ 1

Во-первых, существует семантическая разница. Когда у вас есть соединение, вы говорите, что связь между этими двумя таблицами определяется этим условием. Поэтому в первом примере вы говорите, что таблицы связаны с cd.Company = table2.Name AND table2.Id IN (2728). Когда вы используете предложение WHERE, вы говорите, что отношение определено cd.Company = table2.Name и вам нужны только строки, в которых применяется условие table2.Id IN (2728). Даже если они дают один и тот же ответ, это означает, что программист читает ваш код совсем по-другому.

В этом случае предложение WHERE почти наверняка означает, что вы хотите использовать его.

Во-вторых, на самом деле разница в результате в том случае, если вы используете LEFT JOIN вместо INNER JOIN. Если вы включите второе условие как часть соединения, вы все равно получите строку результатов, если условие не выполнено - вы получите значения из левой таблицы и значения null для правой таблицы. Если вы включаете условие как часть предложения WHERE и это условие терпит неудачу, вы не получите строку вообще.

Вот пример, чтобы продемонстрировать это.

Запрос 1 (ГДЕ):

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');

Ответ 2

SQL происходит из реляционной алгебры.

Один из способов взглянуть на разницу заключается в том, что JOINs - это операции над наборами, которые могут создавать больше записей или меньше записей в результате, чем в исходных таблицах. С другой стороны ГДЕ всегда будет ограничивать количество результатов.

Остальная часть текста является дополнительным объяснением.

Подробнее о типах соединений см. статью.

Когда я сказал, что условие where всегда будет ограничивать результаты, вы должны принять во внимание, что, когда мы говорим о запросах на две (или более) таблицы, вам нужно как-то соединить записи из этих таблиц, даже если их нет JOIN.

Итак, в SQL, если таблицы просто разделены запятой, вы фактически используете CROSS JOIN (декартово произведение), которое возвращает каждую строку из одной таблицы для каждой строки в другой.

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

Но удерживайте, есть исключения из этого максимума, когда вы вводите соединения LEFT, RIGHT и FULL OUTER.

LEFT JOIN объединяет записи из левой таблицы по заданному критерию с записями из правой таблицы, НО, если критерии присоединения, глядя на строку из левой таблицы, не удовлетворяются для любых записей в правой таблице, LEFT JOIN по-прежнему будет возвращать запись из левой таблицы и в столбцах, которые будут поступать из правой таблицы, она вернет NULL (RIGHT JOIN работает аналогично, но с другой стороны, FULL OUTER работает так же, как и в то же время).

Поскольку перекрестное соединение по умолчанию НЕ возвращает эти записи, вы не можете выразить эти критерии присоединения с условием WHERE, и вы вынуждены использовать синтаксис JOIN (оракул был исключением из этого в дополнение к стандарту SQL и to = operator, но это не было принято другими поставщиками или стандартом).

Кроме того, объединения обычно, но не всегда, совпадают с существующей ссылочной целостностью и предполагают отношения между объектами, но я не придавал бы такого большого значения, поскольку условия, где условия могут делать то же самое (за исключением случая, упомянутого выше), и к хорошей СУБД, это не повлияет на то, где вы указываете свои критерии.

Ответ 3

Соединение используется для отражения отношений сущности
предложение where фильтрует результаты.

Таким образом, предложения join являются "статическими" (если только отношения сущностей не изменяются), если предложения where являются специфичными для конкретного случая.

Ответ 4

Нет никакой разницы. "ON" похож на синоним "WHERE", поэтому второй вид читается следующим образом:

JOIN table2 WHERE cd.Company = table2.Name AND table2.Id IN (2728)

Ответ 5

Нет никакой разницы, когда механизм оптимизации запросов разбивает его на соответствующие операторы запроса.