Joing 방법 종류 정리

// 첫번째 Table
mysql> select * from first;
+------+------+------+
| num1 | a    | b    |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    1 |    2 |
|    3 |    2 |    3 |
|    4 |    3 |    5 |
+------+------+------+


// 두번째 Table
mysql> select * from second;
+------+------+------+
| num2 | b    | c    |
+------+------+------+
|    1 |    1 | x    |
|    2 |    4 | y    |
|    3 |    3 | z    |
|    4 |    5 | q    |
|    5 |    7 | a    |
+------+------+------+



// Join

mysql> select first.*, second.* from first, second where first.b = second.b;
+------+------+------+------+------+------+
| num1 | a    | b    | num2 | b    | c    |
+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 | x    |
|    3 |    2 |    3 |    3 |    3 | z    |
|    4 |    3 |    5 |    4 |    5 | q    |
+------+------+------+------+------+------+



mysql> select first.*, second.* from first inner join second on first.b = second.b;
+------+------+------+------+------+------+
| num1 | a    | b    | num2 | b    | c    |
+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 | x    |
|    3 |    2 |    3 |    3 |    3 | z    |
|    4 |    3 |    5 |    4 |    5 | q    |
+------+------+------+------+------+------+



mysql> select first.*, second.* from first natural join second;
+------+------+------+------+------+------+
| num1 | a    | b    | num2 | b    | c    |
+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 | x    |
|    3 |    2 |    3 |    3 |    3 | z    |
|    4 |    3 |    5 |    4 |    5 | q    |
+------+------+------+------+------+------+


====================================================================================== 같은 결과




mysql> select first.*, second.* from first left join second on first.b = second.b;
+------+------+------+------+------+------+
| num1 | a    | b    | num2 | b    | c    |
+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 | x    |
|    2 |    1 |    2 | NULL | NULL | NULL |
|    3 |    2 |    3 |    3 |    3 | z    |
|    4 |    3 |    5 |    4 |    5 | q    |
+------+------+------+------+------+------+


mysql> select first.*, second.* from first left outer join second using(b);
+------+------+------+------+------+------+
| num1 | a    | b    | num2 | b    | c    |
+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 | x    |
|    2 |    1 |    2 | NULL | NULL | NULL |
|    3 |    2 |    3 |    3 |    3 | z    |
|    4 |    3 |    5 |    4 |    5 | q    |
+------+------+------+------+------+------+


mysql> select first.*, second.* from first left join second using (b);
+------+------+------+------+------+------+
| num1 | a    | b    | num2 | b    | c    |
+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 | x    |
|    2 |    1 |    2 | NULL | NULL | NULL |
|    3 |    2 |    3 |    3 |    3 | z    |
|    4 |    3 |    5 |    4 |    5 | q    |
+------+------+------+------+------+------+



====================================================================================== 같은 결과



mysql> select first.*, second.* from first right outer join second using(b);
+------+------+------+------+------+------+
| num1 | a    | b    | num2 | b    | c    |
+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 | x    |
| NULL | NULL | NULL |    2 |    4 | y    |
|    3 |    2 |    3 |    3 |    3 | z    |
|    4 |    3 |    5 |    4 |    5 | q    |
| NULL | NULL | NULL |    5 |    7 | a    |
+------+------+------+------+------+------+



mysql> select first.*, second.* from first natural right join second;
+------+------+------+------+------+------+
| num1 | a    | b    | num2 | b    | c    |
+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 | x    |
| NULL | NULL | NULL |    2 |    4 | y    |
|    3 |    2 |    3 |    3 |    3 | z    |
|    4 |    3 |    5 |    4 |    5 | q    |
| NULL | NULL | NULL |    5 |    7 | a    |
+------+------+------+------+------+------+




mysql> select first.*, second.* from first right join second on first.b=second.b;
+------+------+------+------+------+------+
| num1 | a    | b    | num2 | b    | c    |
+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 | x    |
| NULL | NULL | NULL |    2 |    4 | y    |
|    3 |    2 |    3 |    3 |    3 | z    |
|    4 |    3 |    5 |    4 |    5 | q    |
| NULL | NULL | NULL |    5 |    7 | a    |
+------+------+------+------+------+------+


====================================================================================== 같은 결과