MySQL join 정리

MySQL join

단, MariaDB에서는 Full Join 은 지원하지 않는다. 

아래와 같이 tableA, tableB 의 데이터가 존재한다면

-- tableA data
SELECT * FROM tableA
 a_id  A_col     b_id  
------  ------  --------
     1  a1        (NULL)
     2  a2             1
     3  a3             2
     4  a4             3
     5  a5             4

-- tableB data
SELECT * FROM tableB
  b_id  B_col     a_id  
------  ------  --------
     1  b1             1
     2  b2             2
     3  b3             3
     4  b4             4
     5  b5        (NULL)

-- select <column> from Table A Inner join Table B on A.key = B.key

SELECT * 
FROM tableA a, tableB b
WHERE a.b_id = b.a_id

  a_id  A_col     b_id    b_id  B_col     a_id  
------  ------  ------  ------  ------  --------
     2  a2           1       1  b1             1
     3  a3           2       2  b2             2
     4  a4           3       3  b3             3
     5  a5           4       4  b4             4

-- select <column> from Table A left join Table B on A.key = B.key

SELECT * 
FROM tableA a LEFT JOIN tableB b
ON a.b_id = b.b_id

 a_id  A_col     b_id    b_id  B_col     a_id  
------  ------  ------  ------  ------  --------
     1  a1      (NULL)  (NULL)  (NULL)    (NULL)
     2  a2           1       1  b1             1
     3  a3           2       2  b2             2
     4  a4           3       3  b3             3
     5  a5           4       4  b4             4     

-- select <column> from Table A left join Table B on A.key = B.key where B.key is null

SELECT * 
FROM tableA a LEFT JOIN tableB b
ON a.b_id = b.b_id
WHERE b.b_id IS NULL

  a_id  A_col     b_id    b_id  B_col     a_id  
------  ------  ------  ------  ------  --------
     1  a1      (NULL)  (NULL)  (NULL)    (NULL)

-- select <column> from Table A right join Table B on A.key = B.key
SELECT * 
FROM tableA a RIGHT  JOIN tableB b
ON a.b_id = b.b_id

 a_id  A_col     b_id    b_id  B_col     a_id  
------  ------  ------  ------  ------  --------
     2  a2           1       1  b1             1
     3  a3           2       2  b2             2
     4  a4           3       3  b3             3
     5  a5           4       4  b4             4
(NULL)  (NULL)  (NULL)       5  b5        (NULL)   

-- select <column> from Table A right join Table B on A.key = B.key where A.key is null

SELECT * 
FROM tableA a RIGHT JOIN tableB b
ON a.b_id = b.b_id
WHERE a.b_id IS NULL

  a_id  A_col     b_id    b_id  B_col     a_id  
------  ------  ------  ------  ------  --------
(NULL)  (NULL)  (NULL)       5  b5        (NULL)

-- select <column> from Table A full outer join Table B on A.key = B.key where A.key is null

SELECT * 
FROM tableA a FULL OUTER JOIN tableB b
ON a.b_id = b.b_id

  a_id  A_col     b_id    b_id  B_col     a_id  
------  ------  ------  ------  ------  --------
     1  a1      (NULL)  (NULL)  (NULL)    (NULL)
     2  a2           1       1  b1             1
     3  a3           2       2  b2             2
     4  a4           3       3  b3             3
     5  a5           4       4  b4             4
(NULL)  (NULL)  (NULL)       5  b5        (NULL)

-- select <column> from Table A full outer join Table B on A.key = B.key where A.key is null or B.key is null

SELECT * 
FROM tableA a FULL OUTER JOIN tableB b
ON a.id = b.tableA_id
WHERE a.id IS NULL OR b.tableA_id IS NULL

  a_id  A_col     b_id    b_id  B_col     a_id  
------  ------  ------  ------  ------  --------
     1  a1      (NULL)  (NULL)  (NULL)    (NULL)
(NULL)  (NULL)  (NULL)       5  b5        (NULL)

---