SQL JOIN - предложение WHERE по сравнению с предложением ON

Во внутреннем соединении они означают одно и то же. Однако вы получите разные результаты во внешнем соединении в зависимости от того, поместите ли вы условие соединения в предложение WHERE vs ON. Взгляните на этот связанный вопрос и этот ответ (мной).

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

Это не одно и то же.

Рассмотрим эти запросы:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

а также

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

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

Для INNER JOIN пункты эквивалентны эффективно. Однако то, что они функционально одинаковы, в том смысле, что они дают одинаковые результаты, не означает, что два вида предложений имеют одинаковое семантическое значение.

На INNER JOIN они взаимозаменяемы, и оптимизатор может переставить их по своему желанию.

На OUTER JOIN они не обязательно взаимозаменяемы, в зависимости от того, от какой стороны соединения они зависят.

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

С точки зрения оптимизатора не должно иметь значения, определяете ли вы свои предложения соединения с помощью ON или WHERE.

Однако, IMHO, я думаю, что гораздо понятнее использовать предложение ON при выполнении соединений. Таким образом, у вас есть конкретный раздел вашего запроса, который определяет, как объединение обрабатывается, а не смешивается с остальными предложениями WHERE.

Я делаю это так:

• Всегда помещайте условия соединения в предложение ON, если вы выполняете INNER JOIN. Итак, не добавляйте никаких условий WHERE в предложение ON, поместите их в предложение WHERE.

• Если вы выполняете LEFT JOIN, добавьте любые условия WHERE в предложение ON для таблицы на стороне верно соединения. Это обязательно, потому что добавление предложения WHERE, которое ссылается на правую часть соединения, преобразует соединение во ВНУТРЕННЕЕ СОЕДИНЕНИЕ.

  Исключение составляют случаи, когда вы ищете записи, которых нет в конкретной таблице. Вы можете добавить ссылку на уникальный идентификатор (который никогда не является NULL) в таблице RIGHT JOIN в предложение WHERE следующим образом: WHERE t2.idfield IS NULL. Итак, единственный раз, когда вы должны ссылаться на таблицу с правой стороны соединения, - это найти те записи, которых нет в таблице.

это мое решение.

SELECT song_ID,songs.fullname, singers.fullname
FROM music JOIN songs ON songs.ID = music.song_ID  
JOIN singers ON singers.ID = music.singer_ID
GROUP BY songs.fullname

Вы должны бытьGROUP BY, чтобы заставить его работать.

Надеюсь на эту помощь.

Я думаю, это эффект последовательности соединений. В верхнем левом случае соединения SQL сначала выполняет левое соединение, а затем выполняет фильтр where. В более низком случае сначала найдите Orders.ID = 12345, а затем присоединитесь.

• Не имеет значения для внутренних соединений

• Вопросы для наружных стыков

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

  б. Пункт ON - присоединение До. Записи (из правой таблицы) будут отфильтрованы перед присоединением. Это может закончиться нулевым результатом (так как ВНЕШНЕЕ соединение).

Пример: Рассмотрите следующие таблицы:

    1. documents:
     | id    | name        |
     --------|-------------|
     | 1     | Document1   |
     | 2     | Document2   |
     | 3     | Document3   |
     | 4     | Document4   |
     | 5     | Document5   |


    2. downloads:
     | id   | document_id   | username |
     |------|---------------|----------|
     | 1    | 1             | sandeep  |
     | 2    | 1             | simi     |
     | 3    | 2             | sandeep  |
     | 4    | 2             | reya     |
     | 5    | 3             | simi     |

а) Внутри пункта WHERE:

  SELECT documents.name, downloads.id
    FROM documents
    LEFT OUTER JOIN downloads
      ON documents.id = downloads.document_id
    WHERE username = 'sandeep'

 For above query the intermediate join table will look like this.

    | id(from documents) | name         | id (from downloads) | document_id | username |
    |--------------------|--------------|---------------------|-------------|----------|
    | 1                  | Document1    | 1                   | 1           | sandeep  |
    | 1                  | Document1    | 2                   | 1           | simi     |
    | 2                  | Document2    | 3                   | 2           | sandeep  |
    | 2                  | Document2    | 4                   | 2           | reya     |
    | 3                  | Document3    | 5                   | 3           | simi     |
    | 4                  | Document4    | NULL                | NULL        | NULL     |
    | 5                  | Document5    | NULL                | NULL        | NULL     |

  After applying the `WHERE` clause and selecting the listed attributes, the result will be: 

   | name         | id |
   |--------------|----|
   | Document1    | 1  |
   | Document2    | 3  | 

б) Внутри пункта JOIN

  SELECT documents.name, downloads.id
  FROM documents
    LEFT OUTER JOIN downloads
      ON documents.id = downloads.document_id
        AND username = 'sandeep'

For above query the intermediate join table will look like this.

    | id(from documents) | name         | id (from downloads) | document_id | username |
    |--------------------|--------------|---------------------|-------------|----------|
    | 1                  | Document1    | 1                   | 1           | sandeep  |
    | 2                  | Document2    | 3                   | 2           | sandeep  |
    | 3                  | Document3    | NULL                | NULL        | NULL     |
    | 4                  | Document4    | NULL                | NULL        | NULL     |
    | 5                  | Document5    | NULL                | NULL        | NULL     |

Notice how the rows in `documents` that did not match both the conditions are populated with `NULL` values.

After Selecting the listed attributes, the result will be: 

   | name       | id   |
   |------------|------|
   |  Document1 | 1    |
   |  Document2 | 3    | 
   |  Document3 | NULL |
   |  Document4 | NULL | 
   |  Document5 | NULL | 

В SQL предложения WHERE и ON являются своего рода условными операторами состояний, но основное различие между ними состоит в том, что предложение Where используется в операторах Select / Update для определения условий, тогда как предложение ON используется в объединениях, где он проверяет или проверяет, совпадают ли записи в целевой и исходной таблицах, до объединения таблиц.

Например: - "ГДЕ"

SELECT * FROM employee WHERE employee_id=101

Например: - 'ВКЛ'

Есть две таблицы employee и employee_details, соответствующие столбцы - employee_id.

SELECT * FROM employee 
INNER JOIN employee_details 
ON employee.employee_id = employee_details.employee_id

Надеюсь, я ответил на ваш вопрос. Вернитесь для любых пояснений.

Для внутреннего соединения WHERE и ON могут использоваться взаимозаменяемо. Фактически, можно использовать ON в коррелированном подзапросе. Например:

update mytable
set myscore=100
where exists (
select 1 from table1
inner join table2
on (table2.key = mytable.key)
inner join table3
on (table3.key = table2.key and table3.key = table1.key)
...
)

Это (ИМХО) совершенно сбивает с толку человека, и очень легко забыть связать table1 с чем-либо (потому что в таблице «драйвер» нет пункта «on»), но это законно.

Связь между таблицами

Учитывая, что у нас есть следующие таблицы post и post_comment:

post имеет следующие записи:

| id | title     |
|----|-----------|
| 1  | Java      |
| 2  | Hibernate |
| 3  | JPA       |

а post_comment имеет следующие три ряда:

| id | review    | post_id |
|----|-----------|---------|
| 1  | Good      | 1       |
| 2  | Excellent | 1       |
| 3  | Awesome   | 2       |

SQL ВНУТРЕННЕЕ СОЕДИНЕНИЕ

Предложение SQL JOIN позволяет связывать строки, принадлежащие разным таблицам. Например, КРЕСТНОЕ СОЕДИНЕНИЕ создаст декартово произведение, содержащее все возможные комбинации строк между двумя соединяемыми таблицами.

Хотя CROSS JOIN полезен в определенных сценариях, в большинстве случаев вы хотите объединить таблицы на основе определенного условия. И вот здесь в игру вступает INNER JOIN.

SQL INNER JOIN позволяет нам фильтровать декартово произведение объединения двух таблиц на основе условия, указанного в предложении ON.

SQL INNER JOIN - ON условие "всегда верно"

Если вы предоставите условие «всегда верно», INNER JOIN не будет фильтровать объединенные записи, и набор результатов будет содержать декартово произведение двух объединяемых таблиц.

Например, если мы выполним следующий запрос SQL INNER JOIN:

SELECT
   p.id AS "p.id",
   pc.id AS "pc.id"
FROM post p
INNER JOIN post_comment pc ON 1 = 1

Мы получим все комбинации записей post и post_comment:

| p.id    | pc.id      |
|---------|------------|
| 1       | 1          |
| 1       | 2          |
| 1       | 3          |
| 2       | 1          |
| 2       | 2          |
| 2       | 3          |
| 3       | 1          |
| 3       | 2          |
| 3       | 3          |

Итак, если условие предложения ON "всегда истинно", INNER JOIN просто эквивалентно запросу CROSS JOIN:

SELECT
   p.id AS "p.id",
   pc.id AS "pc.id"
FROM post p
CROSS JOIN post_comment
WHERE 1 = 1
ORDER BY p.id, pc.id

SQL INNER JOIN - ON условие "всегда ложно"

С другой стороны, если условие предложения ON «всегда ложно», то все объединенные записи будут отфильтрованы, и набор результатов будет пустым.

Итак, если мы выполним следующий запрос SQL INNER JOIN:

SELECT
   p.id AS "p.id",
   pc.id AS "pc.id"
FROM post p
INNER JOIN post_comment pc ON 1 = 0
ORDER BY p.id, pc.id

Никакого результата назад мы не получим:

| p.id    | pc.id      |
|---------|------------|

Это потому, что приведенный выше запрос эквивалентен следующему запросу CROSS JOIN:

SELECT
   p.id AS "p.id",
   pc.id AS "pc.id"
FROM post p
CROSS JOIN post_comment
WHERE 1 = 0
ORDER BY p.id, pc.id

SQL INNER JOIN - предложение ON с использованием столбцов внешнего ключа и первичного ключа

Наиболее распространенное условие предложения ON - это то, которое сопоставляет столбец внешнего ключа в дочерней таблице со столбцом первичного ключа в родительской таблице, как показано в следующем запросе:

SELECT
   p.id AS "p.id",
   pc.post_id AS "pc.post_id",
   pc.id AS "pc.id",
   p.title AS "p.title",
   pc.review  AS "pc.review"
FROM post p
INNER JOIN post_comment pc ON pc.post_id = p.id
ORDER BY p.id, pc.id

При выполнении вышеуказанного запроса SQL INNER JOIN мы получаем следующий набор результатов:

| p.id    | pc.post_id | pc.id      | p.title    | pc.review |
|---------|------------|------------|------------|-----------|
| 1       | 1          | 1          | Java       | Good      |
| 1       | 1          | 2          | Java       | Excellent |
| 2       | 2          | 3          | Hibernate  | Awesome   |

Таким образом, в набор результатов запроса включаются только записи, соответствующие условию предложения ON. В нашем случае набор результатов содержит все post вместе с их записями post_comment. Строки post, не связанные с post_comment, исключаются, поскольку они не могут удовлетворять условию предложения ON.

Опять же, приведенный выше запрос SQL INNER JOIN эквивалентен следующему запросу CROSS JOIN:

SELECT
   p.id AS "p.id",
   pc.post_id AS "pc.post_id",
   pc.id AS "pc.id",
   p.title AS "p.title",
   pc.review  AS "pc.review"
FROM post p, post_comment pc
WHERE pc.post_id = p.id

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

| p.id | pc.post_id | pc.id | p.title   | pc.review |
|------|------------|-------|-----------|-----------|
| 1    | 1          | 1     | Java      | Good      |
| 1    | 1          | 2     | Java      | Excellent |
| 1    | 2          | 3     | Java      | Awesome   || 2    | 1          | 1     | Hibernate | Good      || 2    | 1          | 2     | Hibernate | Excellent |
| 2    | 2          | 3     | Hibernate | Awesome   |
| 3    | 1          | 1     | JPA       | Good      || 3    | 1          | 2     | JPA       | Excellent || 3    | 2          | 3     | JPA       | Awesome   |

Заключение

Оператор INNER JOIN можно переписать как CROSS JOIN с предложением WHERE, соответствующим тому же условию, которое вы использовали в предложении ON запроса INNER JOIN.

Not that this only applies to INNER JOIN, not for OUTER JOIN.

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

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

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

Когда дело доходит до левого соединения, существует большая разница между пункт где и по пункту.

Вот пример:

mysql> desc t1; 
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| fid   | int(11)     | NO   |     | NULL    |       |
| v     | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Там fid - это идентификатор таблицы t2.

mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| v     | varchar(10) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Запрос по "on clause":

mysql> SELECT * FROM `t1` left join t2 on fid = t2.id AND t1.v = 'K' 
    -> ;
+----+-----+---+------+------+
| id | fid | v | id   | v    |
+----+-----+---+------+------+
|  1 |   1 | H | NULL | NULL |
|  2 |   1 | B | NULL | NULL |
|  3 |   2 | H | NULL | NULL |
|  4 |   7 | K | NULL | NULL |
|  5 |   5 | L | NULL | NULL |
+----+-----+---+------+------+
5 rows in set (0.00 sec)

Запрос по "where clause":

mysql> SELECT * FROM `t1` left join t2 on fid = t2.id where t1.v = 'K';
+----+-----+---+------+------+
| id | fid | v | id   | v    |
+----+-----+---+------+------+
|  4 |   7 | K | NULL | NULL |
+----+-----+---+------+------+
1 row in set (0.00 sec)

Ясно, что, первый запрос возвращает запись из t1 и зависимую от нее строку из t2, если таковая имеется, для строки t1.v = 'K'.

Второй запрос возвращает строки из t1, но только для t1.v = 'K' будет иметь связанную с ним строку.

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

Я думаю, что это различие лучше всего можно объяснить с помощью логический порядок операций в SQL, который упрощен:

• FROM (включая соединения)
• WHERE
• GROUP BY
• Агрегаты
• HAVING
• WINDOW
• SELECT
• DISTINCT
• UNION, INTERSECT, EXCEPT
• ORDER BY
• OFFSET
• FETCH

Объединения - это не часть оператора select, а оператор внутри FROM. Таким образом, все предложения ON, принадлежащие соответствующему оператору JOIN, «уже имеют» логически к тому времени, когда логическая обработка достигает предложения WHERE. Это означает, что в случае LEFT JOIN, например, семантика внешнего соединения уже возникла к моменту применения предложения WHERE.

Я объяснил следующий пример более подробно в этом сообщении в блоге.. При выполнении этого запроса:

SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id)
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE film_id < 10
GROUP BY a.actor_id, a.first_name, a.last_name
ORDER BY count(fa.film_id) ASC;

LEFT JOIN на самом деле не имеет никакого полезного эффекта, потому что даже если актер не играл в фильме, актер будет отфильтрован, так как его FILM_ID будет NULL, а предложение WHERE отфильтрует такую ​​строку. Результат примерно такой:

ACTOR_ID  FIRST_NAME  LAST_NAME  COUNT
--------------------------------------
194       MERYL       ALLEN      1
198       MARY        KEITEL     1
30        SANDRA      PECK       1
85        MINNIE      ZELLWEGER  1
123       JULIANNE    DENCH      1

Т.е. так же, как если бы мы внутренне соединили две таблицы. Если мы переместим предикат фильтра в предложение ON, он станет критерием для внешнего соединения:

SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id)
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id
  AND film_id < 10
GROUP BY a.actor_id, a.first_name, a.last_name
ORDER BY count(fa.film_id) ASC;

Это означает, что в результате будут присутствовать актеры без фильмов или без фильмов с FILM_ID < 10.

ACTOR_ID  FIRST_NAME  LAST_NAME     COUNT
-----------------------------------------
3         ED          CHASE         0
4         JENNIFER    DAVIS         0
5         JOHNNY      LOLLOBRIGIDA  0
6         BETTE       NICHOLSON     0
...
1         PENELOPE    GUINESS       1
200       THORA       TEMPLE        1
2         NICK        WAHLBERG      1
198       MARY        KEITEL        1

Коротко

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

Давайте рассмотрим эти таблицы:

А

id | SomeData

B

id | id_A | SomeOtherData

id_A - внешний ключ к таблице A

Написание этого запроса:

SELECT *
FROM A
LEFT JOIN B
ON A.id = B.id_A;

Обеспечит такой результат:

/ : part of the result
                                       B
                      +---------------------------------+
            A         |                                 |
+---------------------+-------+                         |
|/////////////////////|///////|                         |
|/////////////////////|///////|                         |
|/////////////////////|///////|                         |
|/////////////////////|///////|                         |
|/////////////////////+-------+-------------------------+
|/////////////////////////////|
+-----------------------------+

То, что находится в A, но не в B, означает, что для B.

Теперь давайте рассмотрим конкретную часть B.id_A и выделим ее из предыдущего результата:

/ : part of the result
* : part of the result with the specific B.id_A
                                       B
                      +---------------------------------+
            A         |                                 |
+---------------------+-------+                         |
|/////////////////////|///////|                         |
|/////////////////////|///////|                         |
|/////////////////////+---+///|                         |
|/////////////////////|***|///|                         |
|/////////////////////+---+---+-------------------------+
|/////////////////////////////|
+-----------------------------+

Написание этого запроса:

SELECT *
FROM A
LEFT JOIN B
ON A.id = B.id_A
AND B.id_A = SpecificPart;

Обеспечит такой результат:

/ : part of the result
* : part of the result with the specific B.id_A
                                       B
                      +---------------------------------+
            A         |                                 |
+---------------------+-------+                         |
|/////////////////////|       |                         |
|/////////////////////|       |                         |
|/////////////////////+---+   |                         |
|/////////////////////|***|   |                         |
|/////////////////////+---+---+-------------------------+
|/////////////////////////////|
+-----------------------------+

Поскольку это удаляет во внутреннем соединении значения, которых нет в B.id_A = SpecificPart.

Теперь давайте изменим запрос на этот:

SELECT *
FROM A
LEFT JOIN B
ON A.id = B.id_A
WHERE B.id_A = SpecificPart;

Результат теперь такой:

/ : part of the result
* : part of the result with the specific B.id_A
                                       B
                      +---------------------------------+
            A         |                                 |
+---------------------+-------+                         |
|                     |       |                         |
|                     |       |                         |
|                     +---+   |                         |
|                     |***|   |                         |
|                     +---+---+-------------------------+
|                             |
+-----------------------------+

Поскольку весь результат фильтруется против B.id_A = SpecificPart, удаляя части B.id_A IS NULL, которые находятся в A, которых нет в B

Вы пытаетесь объединить данные или отфильтровать данные?

Для удобства чтения имеет смысл выделить эти варианты использования в ON и WHERE соответственно.

• объединить данные в ON
• фильтровать данные в WHERE

Может стать очень трудно прочитать запрос, в котором условие JOIN и условие фильтрации существуют в предложении WHERE.

С точки зрения производительности вы не должны увидеть разницы, хотя разные типы SQL иногда обрабатывают планирование запросов по-разному, поэтому стоит попробовать ¯\_(ツ)_/¯ (помните о кешировании, влияющем на скорость запроса)

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

Я написал более подробный пост об этом здесь: https://dataschool.com/learn/difference-between-where-and-on-in-sql

Что касается вашего вопроса,

Это одно и то же как 'on', так и 'where' для внутреннего соединения, если ваш сервер может его получить:

select * from a inner join b on a.c = b.c

а также

select * from a inner join b where a.c = b.c

Вариант «где» известен не всем переводчикам, поэтому, возможно, его следует избегать. И, конечно же, оговорка о включении более ясна.

Они буквально эквивалентны.

В большинстве баз данных с открытым исходным кодом (наиболее заметные примеры в MySql и postgresql) планирование запросов - это вариант классического алгоритма, представленного в Выбор пути доступа в системе управления реляционными базами данных (Селинджер и др., 1979). В этом подходе условия бывают двух типов

• условия, относящиеся к одной таблице (используются для фильтрации)
• условия, относящиеся к двум таблицам (обрабатываются как присоединиться к условиям, независимо от где они появляются)

В частности, в MySql вы можете увидеть себя, отслеживая оптимизатор, чтобы условия join .. on были заменен при разборе эквивалентными условиями where. Аналогичная вещь происходит в postgresql (хотя нет возможности увидеть это в журнале, вы должны прочитать описание источника).

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

Вы можете использовать explain, чтобы убедиться, что они создают идентичные планы. Например, в postgres, план будет содержать пункт join, даже если вы нигде не использовали синтаксис join..on.

Oracle and SQL server are not open source, but, as far as I know, they are based equivalence rules (similar to those in relational algebra), and they also produce identical execution plans in both cases.

Obviously, the two syntax styles are not equivalent for outer joins, for those you have to use the join ... on syntax