SQL에서 조인 순서가 중요합니까?

SQL에서 조인 순서가 중요합니까?

---

성능을 무시하면 아래 쿼리 A와 B에서 동일한 결과를 얻을 수 있습니까? C와 D는 어떻습니까?

-- A
select *
from   a left join b
           on <blahblah>
       left join c
           on <blahblan>


-- B
select *
from   a left join c
           on <blahblah>
       left join b
           on <blahblan>  

-- C
select *
from   a join b
           on <blahblah>
       join c
           on <blahblan>


-- D
select *
from   a join c
           on <blahblah>
       join b
           on <blahblan>  

---

들어 INNER조인, 더, 순서는 중요하지 않습니다 않습니다. 선택 항목을에서 (으) SELECT *로 변경하는 한 쿼리는 동일한 결과를 반환 합니다 SELECT a.*, b.*, c.*.

---

( LEFT, RIGHT또는 FULL) OUTER조인의 경우 순서가 중요하며 업데이트 된 사항은 훨씬 복잡합니다.

첫째, 외부 조인은 교환 적이 지 a LEFT JOIN b않으므로 다음과 같습니다.b LEFT JOIN a

외부 조인도 연관성이 없으므로 예제에서 (정류 성과 연관성) 속성을 모두 포함합니다.

a LEFT JOIN b 
    ON b.ab_id = a.ab_id
  LEFT JOIN c
    ON c.ac_id = a.ac_id

다음과 같습니다 .

a LEFT JOIN c 
    ON c.ac_id = a.ac_id
  LEFT JOIN b
    ON b.ab_id = a.ab_id

그러나:

a LEFT JOIN b 
    ON  b.ab_id = a.ab_id
  LEFT JOIN c
    ON  c.ac_id = a.ac_id
    AND c.bc_id = b.bc_id

다음과 동일하지 않습니다 .

a LEFT JOIN c 
    ON  c.ac_id = a.ac_id
  LEFT JOIN b
    ON  b.ab_id = a.ab_id
    AND b.bc_id = c.bc_id

---

또 다른 (희망적으로 더 간단한) 연관성 예제입니다. 이것을 다음과 같이 생각하십시오 (a LEFT JOIN b) LEFT JOIN c:

a LEFT JOIN b 
    ON b.ab_id = a.ab_id          -- AB condition
 LEFT JOIN c
    ON c.bc_id = b.bc_id          -- BC condition

This is equivalent to a LEFT JOIN (b LEFT JOIN c):

a LEFT JOIN  
    b LEFT JOIN c
        ON c.bc_id = b.bc_id          -- BC condition
    ON b.ab_id = a.ab_id          -- AB condition

only because we have "nice" ON conditions. Both ON b.ab_id = a.ab_id and c.bc_id = b.bc_id are equality checks and do not involve NULL comparisons.

You can even have conditions with other operators or more complex ones like: ON a.x <= b.x or ON a.x = 7 or ON a.x LIKE b.x or ON (a.x, a.y) = (b.x, b.y) and the two queries would still be equivalent.

If however, any of these involved IS NULL or a function that is related to nulls like COALESCE(), for example if the condition was b.ab_id IS NULL, then the two queries would not be equivalent.

---

for regular Joins, it doesn't. TableA join TableB will produce the same execution plan as TableB join TableA (so your C and D examples would be the same)

for left and right joins it does. TableA left Join TableB is different than TableB left Join TableA, BUT its the same than TableB right Join TableA

---

If you try joining C on a field from B before joining B, i.e.:

SELECT A.x, A.y, A.z FROM A 
   INNER JOIN C
       on B.x = C.x
   INNER JOIN b
       on A.x = B.x

your query will fail, so in this case the order matters.

---

Oracle optimizer chooses join order of tables for inner join. Optimizer chooses the join order of tables only in simple FROM clauses . U can check the oracle documentation in their website. And for the left, right outer join the most voted answer is right. The optimizer chooses the optimal join order as well as the optimal index for each table. The join order can affect which index is the best choice. The optimizer can choose an index as the access path for a table if it is the inner table, but not if it is the outer table (and there are no further qualifications).

The optimizer chooses the join order of tables only in simple FROM clauses. Most joins using the JOIN keyword are flattened into simple joins, so the optimizer chooses their join order.

The optimizer does not choose the join order for outer joins; it uses the order specified in the statement.

When selecting a join order, the optimizer takes into account: The size of each table The indexes available on each table Whether an index on a table is useful in a particular join order The number of rows and pages to be scanned for each table in each join order

참고URL : https://stackoverflow.com/questions/9614922/does-the-join-order-matter-in-sql