SQL JOIN-klauzula WHERE vs. klauzula ON

• 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 umieść warunki przyłączenia w klauzuli ON, jeśli robisz INNER JOIN. Nie dodawaj więc żadnych warunków WHERE do klauzuli ON, umieść je w klauzuli WHERE.

• Jeśli robisz LEFT JOIN, Dodaj dowolne warunki WHERE do klauzuli ON dla tabeli w prawo Po stronie łącznika. Jest to konieczne, ponieważ dodanie klauzuli WHERE, która odwołuje się do prawej strony łącznika, spowoduje konwersję łącznika na wewnętrzny Dołącz.

  Wyjątkiem jest, gdy szukasz rekordów, które nie znajdują się w konkretnej tabeli. Można dodać odniesienie do unikalnego identyfikatora (który nie jest zawsze NULL) w prawej tabeli JOIN do klauzuli WHERE w ten sposób: WHERE t2.idfield IS NULL. Tak więc, jedyny raz, kiedy powinieneś odwołać się do tabeli po prawej stronie join, to znaleźć te rekordy, 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.

Relacja tabeli

Biorąc pod uwagę, że mamy następujące tabele post i post_comment:

post posiada następujące rekordy:

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

I post_comment mają następujące trzy wiersze:

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

SQL INNER JOIN

Klauzula SQL JOIN pozwala kojarzyć wiersze należące do różnych tabel. Na przykład, Cross JOIN utworzy iloczyn kartezjański zawierający wszystkie możliwe kombinacje wierszy między dwa stoły łączące.

Podczas gdy łączenie krzyżowe jest przydatne w niektórych scenariuszach, w większości przypadków chcesz łączyć tabele w oparciu o określony warunek. I tam właśnie wkracza INNER JOIN.

Wewnętrzne połączenie SQL pozwala nam filtrować iloczyn kartezjański łączenia dwóch tabel w oparciu o warunek określony w klauzuli ON.

SQL INNER JOIN-on" always true " condition

Jeśli podasz warunek "zawsze prawdziwy", połączenie wewnętrzne nie filtruje połączone rekordy i zbiór wyników będą zawierać iloczyn kartezjański dwóch tabel łączących.

Na przykład, jeśli wykonamy następujące zapytanie 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

Otrzymamy wszystkie kombinacje post i post_comment rekordów:

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

Jeśli warunek klauzuli ON jest "zawsze prawdziwy", wewnętrzne połączenie jest po prostu równoważne zapytaniu krzyżowemu:

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" always false " condition

Z drugiej strony, jeśli klauzula ON warunek jest "always false", wtedy wszystkie połączone rekordy zostaną odfiltrowane i wynik będzie pusty.

Tak więc, jeśli wykonamy następujące zapytanie 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

Nie odzyskamy żadnego wyniku:

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

To dlatego, że powyższe zapytanie jest równoważne z następującym zapytaniem krzyżowym:

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

Najczęstszym warunkiem klauzuli ON jest ten, który pasuje do Kolumna klucza obcego w tabeli podrzędnej z kolumną klucza głównego w tabeli nadrzędnej, jak zilustrowano następującym zapytaniem:

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

Podczas wykonywania powyższego zapytania SQL INNER JOIN otrzymujemy następujący zestaw wyników:

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

Tak więc, tylko rekordy, które pasują do warunku klauzuli ON są zawarte w zestawie wyników zapytania. W naszym przypadku zestaw wyników zawiera wszystkie post wraz z ich post_comment rekordami. Wiersze post, które nie mają powiązanych post_comment, są wykluczone, ponieważ mogą nie spełnia warunku klauzuli 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, post_comment pc
WHERE pc.post_id = p.id

Wiersze niezbite są tymi, które spełniają klauzulę WHERE i tylko te rekordy zostaną uwzględnione w zbiorze wyników. To najlepszy sposób na wizualizację działania klauzuli 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   |

Podsumowanie

Wewnętrzna Instrukcja JOIN może być przepisana jako Cross JOIN z klauzulą WHERE pasującą do tego samego warunku użyłeś w klauzuli ON w zapytaniu INNER JOIN.

Nie to, że dotyczy to tylko połączenia wewnętrznego, Nie połączenia zewnętrznego.

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.

Rozważmy te tabele:

A

id | SomeData

B

id | id_A | SomeOtherData

id_A bycie obcym kluczem do tabeli A

Zapisywanie tego zapytania:

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

Dostarczy wynik:

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

To, co jest w A, ale nie w B, oznacza, że istnieje wartość null dla B.

Rozważmy teraz konkretną część w B.id_A i wyróżnijmy ją z poprzedniego wyniku:

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

Zapisywanie tego zapytania:

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

Dostarczy ten wynik :

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

Ponieważ usuwa w wewnętrznym połączeniu wartości, które nie są w B.id_A = SpecificPart

Teraz zmieńmy zapytanie na to:

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

Wynik jest teraz :

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

Ponieważ cały wynik jest filtrowany przeciwko B.id_A = SpecificPart usuwając części B.id_A IS NULL, które są w A, które nie są w B

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.

Próbujesz dołączyć do danych czy filtrować dane?

Dla czytelności najbardziej sensowne jest wyodrębnienie tych przypadków użycia odpowiednio do ON i WHERE.

• Dołącz dane do
• filtruj dane Gdzie

Odczytywanie zapytania, w którym w klauzuli WHERE występują warunek przyłączenia i warunek filtrowania, może być bardzo trudne.

Pod względem wydajności nie powinieneś widzieć różnicy, chociaż różne typy SQL czasami inaczej obsługują planowanie zapytań dlatego warto spróbować ¯\_(ツ)_/¯ (Należy pamiętać o buforowaniu wpływającym na szybkość zapytania)

Również jak zauważyli inni, jeśli użyjesz zewnętrznego łącznika, otrzymasz Inne wyniki, jeśli umieścisz warunek filtra w klauzuli ON, ponieważ działa on tylko na jedną z tabel.

Napisałem bardziej dogłębny post o tym tutaj: https://dataschool.com/learn/difference-between-where-and-on-in-sql

W SQL, klauzula "WHERE" i " ON "są rodzajem warunkowych Statemantów,ale główną różnicą między nimi jest, 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 w tabelach docelowych i źródłowych, przed połączeniem tabel

Na przykład: - "gdzie"

SELECT * FROM employee WHERE employee_id=101

Na przykład: - ' ON '

istnieją dwa stoły employee_details i employee_details, pasujące kolumny to employee_id.

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

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.

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 rozróżnienie najlepiej wyjaśnić poprzez logiczna kolejność operacji w SQL, czyli w uproszczeniu:

• FROM (łącznie z łącznikami)
• WHERE
• GROUP BY
• agregacje
• HAVING
• WINDOW
• SELECT
• DISTINCT
• UNION, INTERSECT, EXCEPT
• ORDER BY
• OFFSET
• FETCH

Joins nie są klauzulą instrukcji select, ale operator wewnątrz FROM. Jako takie, Wszystkie ON klauzule należące do odpowiedniego operatora JOIN "już się" logicznie do czasu, gdy przetwarzanie logiczne osiągnie klauzulę WHERE. Oznacza to, że w przypadku LEFT JOIN, na przykład, zewnętrzna semantyka join ma już miejsce przed zastosowaniem klauzuli WHERE.

Poniższy przykład wyjaśniłem bardziej szczegółowo w tym poście na blogu. Podczas uruchamiania tego zapytania:

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;

The LEFT JOIN tak naprawdę nie ma żadnego pożytecznego efektu, ponieważ nawet jeśli aktor nie zagrał w filmie, aktor będzie filtrowany, ponieważ jego FILM_ID będzie NULL, a WHERE klauzula filtruje taki wiersz. Wynik jest podobny do:

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

Tzn. tak, jakbyśmy wewnętrznie połączyli dwa stoły. Jeśli przesuniemy predykat filtra w klauzuli ON, staje się on teraz kryterium dla połączenia zewnętrznego:

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;

Co oznacza, że w rezultacie pojawią się aktorzy bez żadnych filmów, lub bez żadnych filmów z 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

W skrócie

Zawsze stawiaj swoje orzeczenie tam, gdzie ma to największy sens, logicznie.

Onesą równoważne , dosłownie.

W większości baz danych open source (najbardziej godne uwagi przykłady, w MySql i postgresql ) planowanie zapytań jest wariantem klasycznego algorytmu pojawiającego się w wyborze ścieżki dostępu W relacyjnym systemie zarządzania bazami danych (Selinger et al, 1979) . W tym podejściu warunki są dwa rodzaje

• warunki odnoszące się do pojedynczej tabeli (używane do filtrowania)
[[19]}warunki odnoszące się do dwie tabele (traktowane jako warunki przyłączenia, niezależnie od gdzie się pojawiają )

Szczególnie w MySql, możesz zobaczyć siebie , śledząc optymalizator, że Warunki join .. on są zastąpione podczas parsowania przez równoważne warunki where. Podobnie dzieje się w postgresql (choć nie ma sposobu, aby zobaczyć to przez dziennik, musisz przeczytać opis źródła).

W każdym razie, najważniejsze jest to, że różnica między tymi dwoma warianty składni są tracone podczas fazy parsowania / przepisywania zapytań, nie docierają nawet do fazy planowania i wykonywania zapytań. Nie ma więc wątpliwości, czy są one równoważne pod względem wydajności, stają się identyczne na długo przed osiągnięciem fazy realizacji.

Możesz użyć explain, aby sprawdzić, czy produkują identyczne plany. Np. w postgres, plan będzie zawierał klauzulę join, nawet jeśli nie użyłeś składni join..on anywhere .

Oracle i SQL server nie są open source, ale, o ile wiem, są oparte na regułach równoważności (podobnych do tych w algebrze relacyjnej), a także wytwarzają identyczne plany wykonania w obu przypadkach.

Oczywiście, dwa style składni nie są równoważne dla złączy zewnętrznych, dla których musisz użyć składni join ... on

Jeśli chodzi o twoje pytanie,]}

Jest to to samo zarówno "on", jak i "where" na łączu wewnętrznym, o ile twój serwer może to uzyskać:

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

I

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

Opcja "gdzie" nie wszyscy tłumacze znają, więc być może należy jej unikać. I oczywiście klauzula " on " jest jaśniejsza.

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.