[SQL] JOIN - INNER JOIN vs OUTER JOIN and CORSS JOIN)

 

CORSS JOIN (aka 데카르트 곱, Cartesian Product, 합집합)

• 크로스조인은 쿼리에 on을 쓰지 않는다.
• 따라서, 각 열의 모든 조합을 return 한다.
• 따라서 다음 A, B 테이블의 크로스 조인의 결과는 아래와 같다.다만, INNER JOIN과 OUTER JOIN은 술어에 'ON'절을 사용한다.

• 'ON'절의 조건을 톤해 얻은 값을 나열하는 방식에 따라 INNER JOIN과 OUTUER JOIN으로 나뉜다.
• OUTER JOIN은 다시 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN으로 나뉜다. 
• INNER JOIN : CROSS JOIN 결과의 모든 행에 대해 'ON' 절의 조건을 계산 -> true인 값을 return.
• LEFT OUTER JOIN : INNER JOIN의 결과 값 + LEFT 테이블에 대한 'ON'절의 RIGTH 테이블 결과가 NULL 값을 return.
• RIGHT OUTER JOIN : INNER JOIN의 결과 값 + RIGHT 테이블에 대한 'ON'절의 LEFT 테이블 결과가 NULL 값을 return.
• FULL OUTER JOIN : INNER JOIN의 결과 값 + 좌,우 OUTER JOIN의 결과를 return.

  ---

INNER JOIN - OUTER JOIN 차이

 

중복이 없는 서로 다른 두 컬럼 A, B를 JOIN할 때 :

• INNER JOIN : A와 B의 교집합(일반적인경우).

• INNER JOIN의 'ON'절은 반드시 '=' 조건일 필요는 없다.
• 또한, 두 테이블을 참조하지 않아도 된다. 

SELECT A.Colour, B.Colour FROM A INNER JOIN A.Colour NOT IN ('Green','Blue')     /* 1 */ 
 
SELECT A.Colour, B.Colour FROM A INNER JOIN B ON 1 =1   /* 2 */

1번 SQL의 결과

2번 SQL의 결과

 

• OUTER JOIN : A와 B의 합집합(일반적인 경우).

• OUTER JOIN의 조회원리는 INNER JOIN과 같으며, 여기에 추가로 조인되지 않은 열을 NULL 값을 사용하여 return한다.
•  

• 1

  SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour

  c

• 

  LEFT OUTER JOIN 결과

• SELECT A.Colour, B.Colour FROM A RIGHT OUTER JOIN B 
                              ON A.Colour = B.Colour

• 

  RIGHT OUTER JOIN의 결과

• OUTER JOIN에서 'ON'절에 조건을 주거나 조건절에 NULL을 활용하여 ANTI SEMI JOIN을 실행할 수 있다.

• SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B 
    ON A.Colour = B.Colour WHERE B.Colour IS NULL

• 

  ANTI JOIN 결과

• SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON A.Colour = B.Colour

• 

  FULL OUTER JOIN

• SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON 1 = 0

• 위 쿼리의 'ON'절의 조건은 FALSE이다. 따라서 JOIN이 되지 않는 열을 return, 결과는 다음과 같다.

• 

  .

• 위의 쿼리를 변경하여 하나의 테이블로 합칠 수 있다.

• SELECT COALESCE(A.Colour, B.Colour) AS Colour FROM A FULL OUTER JOIN B ON 1 = 0

• 

  .

• 조건의 위치에 따른 결과 

• SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B 
                              ON A.Colour = B.Colour WHERE B.Colour = 'Green'
   
   
  SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B 
                              ON A.Colour = B.Colour AND B.Colour = 'Green'

• 

  1행의 결과

• 

  5행의 결과

 

 

출처: stackoverflow - Martin Smith

https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join/38578#38578