JOIN 또는 WHERE 내의 조건

JOIN 또는 WHERE 내의 조건

---

JOIN 절과 WHERE 절에 조건을 넣는 것 사이에 차이 (성능, 모범 사례 등)가 있습니까?

예를 들어 ...

-- Condition in JOIN
SELECT *
FROM dbo.Customers AS CUS
INNER JOIN dbo.Orders AS ORD 
ON CUS.CustomerID = ORD.CustomerID
AND CUS.FirstName = 'John'

-- Condition in WHERE
SELECT *
FROM dbo.Customers AS CUS
INNER JOIN dbo.Orders AS ORD 
ON CUS.CustomerID = ORD.CustomerID
WHERE CUS.FirstName = 'John'

당신은 어느 것을 선호합니까?

---

관계형 대수를 사용하면 WHERE절과 의 술어를 교환 할 수 INNER JOIN있으므로 절이있는 INNER JOIN쿼리 조차도 WHERE옵티 마이저에 의해 술어가 재 배열되어 프로세스 중에 이미 제외 될 수 있습니다JOIN .

가장 읽기 쉬운 방법으로 쿼리를 작성하는 것이 좋습니다.

때로는 여기에는 INNER JOIN상대적으로 "불완전한"작성 및 WHERE필터링 기준 목록을보다 쉽게 ​​유지 보수 할 수 있도록하기 위해 일부 기준을 포함시키는 것이 포함됩니다.

예를 들어,

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
    AND c.State = 'NY'
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
    AND a.Status = 1

쓰다:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
WHERE c.State = 'NY'
    AND a.Status = 1

그러나 그것은 물론 다릅니다.

---

내부 조인의 경우 실제로 차이를 느끼지 못했습니다 (그러나 모든 성능 조정과 마찬가지로 조건에 따라 데이터베이스를 확인해야합니다).

그러나 왼쪽 또는 오른쪽 조인을 사용하는 경우 조건을 배치하는 위치에 큰 차이가 있습니다. 예를 들어 다음 두 쿼리를 고려하십시오.

SELECT *
FROM dbo.Customers AS CUS 
LEFT JOIN dbo.Orders AS ORD 
ON CUS.CustomerID = ORD.CustomerID
WHERE ORD.OrderDate >'20090515'

SELECT *
FROM dbo.Customers AS CUS 
LEFT JOIN dbo.Orders AS ORD 
ON CUS.CustomerID = ORD.CustomerID
AND ORD.OrderDate >'20090515'

첫 번째는 2009 년 5 월 15 일 이후의 주문이있는 레코드 만 제공하므로 왼쪽 조인을 내부 조인으로 변환합니다. 두 번째는 해당 레코드와 주문이없는 고객을 제공합니다. 조건 설정 위치에 따라 결과 집합이 매우 다릅니다. (예를 들어 목적 코드 인 경우에만 프로덕션 코드에서 사용하지 않아야하는 경우 *를 선택하십시오.) 예외는 한 테이블의 레코드 만보고 다른 테이블은보고 싶지 않은 경우입니다. 그런 다음 조인이 아닌 조건에 where 절을 사용하십시오.

SELECT *
FROM dbo.Customers AS CUS 
LEFT JOIN dbo.Orders AS ORD 
ON CUS.CustomerID = ORD.CustomerID
WHERE ORD.OrderID is null

---

Most RDBMS products will optimize both queries identically. In "SQL Performance Tuning" by Peter Gulutzan and Trudy Pelzer, they tested multiple brands of RDBMS and found no performance difference.

I prefer to keep join conditions separate from query restriction conditions.

If you're using OUTER JOIN sometimes it's necessary to put conditions in the join clause.

---

WHERE will filter after the JOIN has occurred.

Filter on the JOIN to prevent rows from being added during the JOIN process.

---

I prefer the JOIN to join full tables/Views and then use the WHERE To introduce the predicate of the resulting set.

It feels syntactically cleaner.

---

I typically see performance increases when filtering on the join. Especially if you can join on indexed columns for both tables. You should be able to cut down on logical reads with most queries doing this too, which is, in a high volume environment, a much better performance indicator than execution time.

I'm always mildly amused when someone shows their SQL benchmarking and they've executed both versions of a sproc 50,000 times at midnight on the dev server and compare the average times.

---

Putting the condition in the join seems "semantically wrong" to me, as that's not what JOINs are "for". But that's very qualitative.

Additional problem: if you decide to switch from an inner join to, say, a right join, having the condition be inside the JOIN could lead to unexpected results.

---

Joins are quicker in my opinion when you have a larger table. It really isn't that much of a difference though especially if you are dealing with a rather smaller table. When I first learned about joins, i was told that conditions in joins are just like where clause conditions and that i could use them interchangeably if the where clause was specific about which table to do the condition on.

---

It is better to add the condition in the Join. Performance is more important than readability. For large datasets, it matters.

참고URL : https://stackoverflow.com/questions/1018952/condition-within-join-or-where