SQL join: where klauzula vs. on klauzula

• nie ma znaczenia dla połączeń wewnętrznych

• Sprawy dla złączy zewnętrznych

  A. WHERE klauzula: po dołączeniu. Rekordy będą filtrowane po połączeniu.

  B. ON klauzula - przed dołączeniem. Rekordy (z prawej tabeli) będą filtrowane przed dołączeniem. To może skończyć się jako null w wyniku (od OUTER join).

Przykład : rozważmy poniższe tabele:

    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) Wewnątrz WHERE klauzula:

  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) wewnątrz JOIN klauzuli

  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 | 

Na INNER JOIN S są wymienne, a optymalizator zmieni je do woli.

Na OUTER JOINs, niekoniecznie są wymienne, w zależności od tego, od której strony połączenia zależą.

Umieszczam je w dowolnym miejscu w zależności od czytelności.

The way I do it is:

Zawsze umieszczaj warunki przyłączenia w klauzuli on Jeśli robisz połączenie wewnętrzne, więc nie dodawaj żadnych warunków where do klauzuli on, umieść je w klauzuli where

Jeśli robisz lewe połączenie, dodaj dowolne warunki where do klauzuli on dla tabeli po prawej stronie połączenia. Jest to konieczne, ponieważ dodanie klauzuli where, która odwołuje się do prawej strony łącznika, spowoduje konwersję łącznika na łącznik wewnętrzny (z jednym wyjątkiem opisanym poniżej).

Wyjątkiem jest to, że gdy szukasz rekordów, które nie znajdują się w konkretnej tabeli, dodasz refernce do unikalnego identyfikatora (który nigdy nie jest null) w prawej tabeli join do klauzuli where w ten sposób " Where T2.idfield jest null". Tak więc jedynym czasem, kiedy powinieneś odwołać się do tabeli po prawej stronie join, jest znalezienie tych rekordów, których nie ma w tabeli.

Na wewnętrznym połączeniu oznaczają to samo. Jednak otrzymasz różne wyniki w zewnętrznym połączeniu w zależności od tego, czy umieścisz warunek połączenia w klauzuli WHERE vs on. Spójrz na to powiązane pytanie i ta odpowiedź (przeze mnie).

Myślę, że to ma największy sens, aby być w zwyczaju zawsze umieścić warunek join w klauzuli ON (chyba że jest to zewnętrzne join i rzeczywiście chcesz go w klauzuli where), ponieważ to sprawia, że jaśniejsze dla każdego czytającego Twoje zapytanie, na jakich warunkach są łączone tabele, a także pomaga zapobiec temu, aby klauzula WHERE była długa na dziesiątki linii.

Ten artykuł jasno wyjaśnia różnicę. Wyjaśnia również "ON joined_condition vs WHERE joined_condition or joined_alias is null".

Klauzula WHERE filtruje zarówno lewą,jak i prawą stronę połączenia, podczas gdy klauzula ON zawsze filtruje tylko prawą stronę.

1. Jeśli zawsze chcesz pobrać wiersze z lewej strony i połączyć tylko wtedy, gdy jakiś warunek pasuje, powinieneś użyć klauzuli ON.
2. Jeśli chcesz filtrować produkt połączenia obu strony, Następnie należy użyć klauzuli WHERE.

Jeśli chodzi o optymalizator, nie powinno mieć znaczenia, czy definiujesz klauzule przyłączenia za pomocą ON lub WHERE.

Jednak IMHO, myślę, że dużo jaśniej jest używać klauzuli ON podczas wykonywania połączeń. W ten sposób masz określoną sekcję zapytania, która dyktuje sposób obsługi połączenia w porównaniu z resztą klauzul WHERE.

Jest wielka różnica między klauzula where vs. w sprawie klauzuli, / align = "left" /

Oto przykład:

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

Tam fid jest id tabeli 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)

Zapytanie o "klauzula 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)

Zapytanie dotyczące klauzuli "gdzie":

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)

Jest jasne, że, pierwsze zapytanie zwraca rekord z t1 i jego zależny wiersz z T2, jeśli istnieje, dla wiersza t1.v = "K".

Drugie zapytanie zwraca wiersze od t1, ale tylko dla t1.v = ' K ' będzie miał każdy powiązany wiersz z nim.

W SQL klauzula "WHERE" i " ON "są rodzajem warunkowych Statemantów, ale główną różnicą między nimi jest to,że klauzula "Where" jest używana w instrukcjach Select/Update do określania warunków, podczas gdy Klauzula " ON " jest używana w połączeniach, gdzie weryfikuje lub sprawdza, czy rekordy są dopasowane do tabel docelowych i źródłowych, przed połączeniem tabel

Na przykład: - "gdzie"

SELECT * FROM WHERE employee_id=101

Na przykład: - ' ON '

* istnieją dwie tabele employee_details employee_ i employee_details, pasujące kolumny to employee_id.*

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

Mam nadzieję, że odpowiedziałem na twoje pytanie.Wróć do wyjaśnienia.

Dla lepszej wydajności tabele powinny mieć specjalną indeksowaną kolumnę do użycia dla złączeń .

Więc jeśli kolumna, którą podajesz, nie jest jedną z tych indeksowanych kolumn, to podejrzewam, że lepiej trzymać ją w miejscu, w którym .

Więc łączysz używając zindeksowanych kolumn, następnie po połączeniu uruchamiasz warunek na bezindeksowanej kolumnie .

Zwykle filtrowanie jest przetwarzane w klauzuli WHERE po połączeniu obu tabel. Jest to możliwe, ale możesz chcieć przefiltrować jedną lub obie tabele przed dołączeniem do nich. tzn. klauzula where ma zastosowanie do całego zbioru wyników, podczas gdy klauzula on ma zastosowanie tylko do danego połączenia.

Myślę, że to efekt join sequence. W lewym górnym rogu przypadku połączenia, SQL do lewej dołączyć pierwszy, a następnie zrobić gdzie filtr. W tym przypadku najpierw znajdź Orders. ID = 12345, a następnie dołącz.

Dla połączenia wewnętrznego, WHERE i ON mogą być używane zamiennie. W rzeczywistości możliwe jest użycie ON w skorelowanym zapytaniu podrzędnym. Na przykład:

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)
...
)

Jest to (IMHO) całkowicie mylące dla człowieka i bardzo łatwo zapomnieć o połączeniu table1 z czymkolwiek (ponieważ tabela "sterownik" nie ma klauzuli "on"), ale jest to legalne.

To jest moje rozwiązanie.

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

TY musisz mieć GROUP BY aby to zadziałało.