Присоединение к SQL: где предложение против предложения

Ответ 1

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

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

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 статьи фактически эквивалентны. Однако только потому, что они функционально одинаковы, поскольку они дают одни и те же результаты, это не значит, что два вида предложений имеют одинаковый смысловой смысл.

Ответ 2

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

• Вопросы для внешних соединений

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

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

Пример. Рассмотрим приведенные ниже таблицы:

    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     |

a) Внутри предложения 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  | 

b) Внутри предложения 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 | 

Ответ 3

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

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

Я помещаю их в любом месте в зависимости от читаемости.

Ответ 4

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

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

• Если вы делаете LEFT JOIN, добавьте любые условия WHERE в предложение ON для таблицы в правой части соединения. Это необходимо, поскольку добавление предложения WHERE, которое ссылается на правую сторону объединения, преобразует соединение в INNER JOIN.

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

Ответ 5

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

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

Ответ 6

Эта статья ясно объясняет разницу. Это также объясняет "ON join_condition против WHERE join_condition или join_alias имеет значение null".

Предложение WHERE фильтрует результат предложения FROM вместе с JOIN, а предложение ON используется для получения результата таблицы между таблицами FROM и JOIN.

1. Если вы хотите получить результат таблицы, который объединяет две таблицы, тогда вам следует воспользоваться предложением ON, чтобы определить, как таблицы объединяются. Конечно, это также может фильтровать строки из исходной таблицы, например, в случае INNER JOIN.
2. Если вы хотите отфильтровать продукт объединения обеих сторон, вам следует использовать предложение WHERE.

Ответ 7

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

Вот пример:

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":

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 where":

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' будет иметь любую связанную с ним строку.

Ответ 8

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

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

Ответ 9

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

Ответ 10

В SQL предложения "WHERE" и "ON" являются разновидностями условных выражений состояния, но основное отличие между ними заключается в том, что выражение "Where" используется в операторах выбора/обновления для указания условий, тогда как предложение "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

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

Ответ 11

Для внутреннего соединения 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)
...
)

Это (IMHO), совершенно сбивающее с толку человека, и очень легко забыть связать table1 с чем угодно (потому что в таблице "драйвер" нет предложения "on"), но это законно.

Ответ 12

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

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

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

Ответ 13

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

Ответ 14

Я думаю, что это различие лучше всего объяснить с помощью логического порядка операций в 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

Короче

Всегда ставьте свой предикат там, где это логично.

Ответ 15

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

id | SomeData

В

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 и 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 = NULL, которые находятся в A, а не в B

Ответ 16

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

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

• объединить данные в ПО
• фильтровать данные в ГДЕ

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

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

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

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

Ответ 17

SQL соединяется с графикой запросов и информации. Различные типы соединений SQL

Ответ 18

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

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, чтобы он работал.

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