SQL 조인 : where 절 대 on 절

Nice programing

SQL 조인 : where 절 대 on 절

nicepro 2020. 10. 2. 23:18

SQL 조인 : where 절 대 on 절

그것을 읽은 후에 이것은 명시 적 SQL 조인과 암시 적 SQL 조인 의 중복 이 아닙니다 . 대답은 관련이 있거나 같을 수 있지만 질문 은 다릅니다.

차이점은 무엇이며 각각에 무엇이 들어가야합니까?

이론을 올바르게 이해했다면 쿼리 최적화 프로그램은 두 가지를 서로 바꿔서 사용할 수 있어야합니다.

그들은 같은 것이 아닙니다.

다음 쿼리를 고려하십시오.

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절이 사실상 동일합니다. 그러나 기능적으로 동일하다고해서 동일한 결과를 생성한다고해서 두 종류의 절이 동일한 의미를 갖는 것은 아닙니다.

내부 조인에는 중요하지 않습니다.
외부 결합에 관한 사항

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

에서 INNER JOIN의 그들은 상호 교환하고, 최적화는 의지에서 다시 정렬됩니다.

일 OUTER JOINS, 그들이 의존 조인의 어느 측면에 따라서, 반드시 교환 가능하지 않다.

가독성에 따라 어느 위치 에나 두었습니다.

내가하는 방법은 :

를 ON수행하는 경우 항상 절에 조인 조건을 입력하십시오 INNER JOIN. 따라서 ON 절에 WHERE 조건을 추가하지 말고 절에 넣으십시오 WHERE.
를 수행하는 경우 조인 오른쪽 에있는 테이블 LEFT JOIN의 ON절에 WHERE 조건을 추가합니다 . 조인의 오른쪽을 참조하는 WHERE 절을 추가하면 조인이 INNER JOIN으로 변환되므로 이는 필수입니다.

예외는 특정 테이블에없는 레코드를 찾는 경우입니다. RIGHT JOIN 테이블의 고유 식별자 (NULL이 아님)에 대한 참조를 다음과 같이 WHERE 절에 추가합니다 WHERE t2.idfield IS NULL.. 따라서 조인의 오른쪽에있는 테이블을 참조해야하는 유일한 시간은 테이블에없는 레코드를 찾는 것입니다.

내부 조인에서도 같은 의미입니다. 그러나 WHERE 대 ON 절에 조인 조건을 넣었는지 여부에 따라 외부 조인에서 다른 결과를 얻을 수 있습니다. 한 번 봐 가지고 이 관련 질문 과 이 답변 (내게로)을.

항상 ON 절에 조인 조건을 두는 습관을 갖는 것이 가장 합리적이라고 생각합니다 (외부 조인이고 실제로 where 절에서 원하는 경우 제외) 쿼리를 읽는 모든 사람에게 더 명확합니다. 테이블이 조인되는 조건 및 WHERE 절이 수십 줄 길이가되는 것을 방지하는 데 도움이됩니다.

이 기사 는 차이점을 명확하게 설명합니다. 또한 "ON joined_condition vs WHERE joined_condition 또는 joined_alias is null"에 대해서도 설명합니다.

WHERE 절은 JOIN과 함께 FROM 절의 결과를 필터링하는 반면 ON 절은 FROM 및 JOIN 테이블 간의 테이블 결과를 생성하는 데 사용됩니다.

두 테이블을 조인하는 테이블 결과를 생성하려면 ON 절을 사용하여 테이블이 조인되는 방법을 결정해야합니다. 물론 이것은 예를 들어 INNER JOIN의 경우 원래 테이블의 행을 필터링 할 수도 있습니다.
양쪽을 결합한 결과를 필터링하려면 WHERE 절을 사용해야합니다.

왼쪽 조인 과 관련 하여 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    |       |
+-------+-------------+------+-----+---------+-------+

테이블 t2의 id가 있습니다.

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 절"에 대한 쿼리 :

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.v = 'K'행에 대해 t1의 레코드와 t2의 종속 행 (있는 경우)을 반환합니다.

두 번째 쿼리는 t1에서 행을 반환하지만 t1.v = 'K'에 대해서만 관련 행이 있습니다.

옵티 마이저 측면에서 ON 또는 WHERE로 조인 절을 정의하는지 여부는 차이가 없어야합니다.

그러나 IMHO, 조인을 수행 할 때 ON 절을 사용하는 것이 훨씬 더 명확하다고 생각합니다. 이렇게하면 조인이 처리되는 방식과 나머지 WHERE 절과 혼합되는 방식을 지시하는 쿼리의 특정 섹션이 있습니다.

이러한 테이블을 고려해 보겠습니다.

ㅏ

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에 대해 null 값이 있음을 의미합니다.

이제에서 특정 부분을 고려 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         |                                 |
+---------------------+-------+                         |
|                     |       |                         |
|                     |       |                         |
|                     +---+   |                         |
|                     |***|   |                         |
|                     +---+---+-------------------------+
|                             |
+-----------------------------+

Because the whole result is filtered against B.id_A = SpecificPart removing the parts B.id_A = NULL, that are in the A that aren't in B

Are you trying to join data or filter data?

For readability it makes the most sense to isolate these use cases to ON and WHERE respectively.

join data in ON
filter data in WHERE

It can become very difficult to read a query where the JOIN condition and a filtering condition exist in the WHERE clause.

Performance wise you should not see a difference, though different types of SQL sometimes handle query planning differently so it can be worth trying ¯\_(ツ)_/¯ (Do be aware of caching effecting the query speed)

Also as others have noted, if you use an outer join you will get different results if you place the filter condition in the ON clause because it only effects one of the tables.

I wrote a more in depth post about this here: https://dataschool.com/learn/difference-between-where-and-on-in-sql

I think it's the join sequence effect. In the upper left join case, SQL do Left join first and then do where filter. In the downer case, find Orders.ID=12345 first, and then do join.

For an inner join, WHERE and ON can be used interchangeably. In fact, it's possible to use ON in a correlated subquery. For example:

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

This is (IMHO) utterly confusing to a human, and it's very easy to forget to link table1 to anything (because the "driver" table doesn't have an "on" clause), but it's legal.

for better performance tables should have a special indexed column to use for JOINS .

so if the column you condition on is not one of those indexed columns then i suspect it is better to keep it in WHERE .

so you JOIN using the indexed columns, then after JOIN you run the condition on the none indexed column .

Normally, filtering is processed in the WHERE clause once the two tables have already been joined. It’s possible, though that you might want to filter one or both of the tables before joining them. i.e, the where clause applies to the whole result set whereas the on clause only applies to the join in question.

In SQL, the 'WHERE' and 'ON' clause,are kind of Conditional Statemants, but the major difference between them are, the 'Where' Clause is used in Select/Update Statements for specifying the Conditions, whereas the 'ON' Clause is used in Joins, where it verifies or checks if the Records are Matched in the target and source tables, before the Tables are Joined

For Example: - 'WHERE'

SELECT * FROM employee WHERE employee_id=101

For Example: - 'ON'

There are two tables employee and employee_details, the matching columns are employee_id.

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

Hope I have answered your Question. Revert for any clarifications.

I think this distinction can best be explained via the logical order of operations in SQL, which is, simplified:

FROM (including joins)
WHERE
GROUP BY
Aggregations
HAVING
WINDOW
SELECT
DISTINCT
UNION, INTERSECT, EXCEPT
ORDER BY
OFFSET
FETCH

Joins are not a clause of the select statement, but an operator inside of FROM. As such, all ON clauses belonging to the corresponding JOIN operator have "already happened" logically by the time logical processing reaches the WHERE clause. This means that in the case of a LEFT JOIN, for example, the outer join's semantics has already happend by the time the WHERE clause is applied.

I've explained the following example more in depth in this blog post. When running this query:

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 doesn't really have any useful effect, because even if an actor did not play in a film, the actor will be filtered, as its FILM_ID will be NULL and the WHERE clause will filter such a row. The result is something like:

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

I.e. just as if we inner joined the two tables. If we move the filter predicate in the ON clause, it now becomes a criteria for the outer join:

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;

Meaning the result will contain actors without any films, or without any films with 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

In short

Always put your predicate where it makes most sense, logically.

this is my solution.

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

You must have the GROUP BY to get it to work.

Hope this help.

참고URL : https://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause

'Nice programing' 카테고리의 다른 글

PHP 구문 분석 / 구문 오류; (0)	2020.10.02
원시 유형은 무엇이며 왜 사용하지 않아야합니까? (0)	2020.10.02
pg_config 실행 파일을 찾을 수 없습니다. (0)	2020.10.02
iOS 8에서 작동하지 않는 위치 서비스 (0)	2020.10.02
Cache-Control : max-age = 0과 no-cache의 차이점은 무엇입니까? (0)	2020.10.02

현재글SQL 조인 : where 절 대 on 절

nicepro

SQL 조인 : where 절 대 on 절

SQL 조인 : where 절 대 on 절

In short

'Nice programing' 카테고리의 다른 글

'Nice programing'의 다른글

티스토리툴바

SQL 조인 : where 절 대 on 절

SQL 조인 : where 절 대 on 절

In short

'Nice programing' 카테고리의 다른 글

'Nice programing'의 다른글

관련글

티스토리툴바