행복한 스마일맨 ^^*

 

ANSI Query(SQL)

• ANSI(American National Standards Institute, 미국 국립 표준 협회)에서 제시한 SQL에 대한 보편적인 문법

※ https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

 

---

 

오라클 Join 예제.

 

테이블 : T_MEMBER

 

테이블 : T_DEPARTMENT

 

---

 

Inner Join
-- (교집합) 각 테이블에서 조인 조건에 일치되는 데이터만 가져온다.

(ANSI) SELECT m.*, d.* FROM T_MEMBER m INNER JOIN T_DEPARTMENT d ON m.DEPT_IDX = d.DEPT_IDX;
(Oralce) SELECT m.*, d.* FROM T_MEMBER m, T_DEPARTMENT d WHERE m.DEPT_IDX = d.DEPT_IDX;

 

 

 

Outer Join
-- 조인 조건에 일치하는 데이터 및 일치하지 않은 데이터를 모두 SELECT 한다.
-- 조인 조건에 일치하는 데이터가 없다면 NULL로 가져온다.
-- 주(main) 테이블이 어떤 테이블인지가 중요하다.

 

• Left Outer Join

(ANSI) SELECT m.*, d.* FROM T_MEMBER m LEFT OUTER JOIN T_DEPARTMENT d ON m.DEPT_IDX = d.DEPT_IDX ORDER BY m.MEM_ID;
(Oralce) SELECT m.*, d.* FROM T_MEMBER m, T_DEPARTMENT d WHERE m.DEPT_IDX = d.DEPT_IDX(+) ORDER BY m.MEM_ID;

 

• Right Outer Join

(ANSI) SELECT m.*, d.* FROM T_MEMBER m RIGHT OUTER JOIN T_DEPARTMENT d ON m.DEPT_IDX = d.DEPT_IDX ORDER BY m.MEM_ID;
(Oralce) SELECT m.*, d.* FROM T_MEMBER m, T_DEPARTMENT d WHERE m.DEPT_IDX(+) = d.DEPT_IDX ORDER BY m.MEM_ID;

 

• Full Outer Join

(ANSI) SELECT m.*, d.* FROM T_MEMBER m FULL OUTER JOIN T_DEPARTMENT d ON m.DEPT_IDX = d.DEPT_IDX ORDER BY m.MEM_ID;
(Oralce) X

 

 

Cross Join
-- 두 테이블 조인 시 가능한 모든 경우의 레코드를 SELECT 한다.
-- 경우의 수 : N * M

(ANSI) SELECT m.*, d.* FROM T_MEMBER m CROSS JOIN T_DEPARTMENT d ORDER BY m.MEM_ID, d.DEPT_IDX;
(Oralce) SELECT m.*, d.* FROM T_MEMBER m, T_DEPARTMENT d ORDER BY m.MEM_ID, d.DEPT_IDX;

 

Self Join
-- 테이블이 자기 자신을 마치 다른 테이블처럼 취급하여 조인한다.

(ANSI) SELECT m.*, d.* FROM T_MEMBER m JOIN T_MEMBER d ON m.MEM_ID = d.MEM_ID ORDER BY m.MEM_ID;
(Oralce) SELECT m.*, d.* FROM T_MEMBER m, T_MEMBER d WHERE m.MEM_ID = d.MEM_ID ORDER BY m.MEM_ID;

 

 

 

// 첫번째 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    |
+------+------+------+------+------+------+


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

 mysql> select a.*, b.* from a inner join b on a.aa = b.ba;
+----+------+------+----+------+------+
| aa | ab   | ac   | ba | bb   | bc   |
+----+------+------+----+------+------+
|  1 |  100 | a1   |  1 |  100 | b1   |
|  3 |  300 | a3   |  3 |  300 | b2   |
|  5 |  500 | a5   |  5 |  500 | b3   |
+----+------+------+----+------+------+
3 rows in set (0.00 sec)

mysql> select a.*, b.* from a left outer join b on a.aa = b.ba;
+----+------+------+------+------+------+
| aa | ab   | ac   | ba   | bb   | bc   |
+----+------+------+------+------+------+
|  1 |  100 | a1   |    1 |  100 | b1   |
|  2 |  200 | a2   | NULL | NULL | NULL |
|  3 |  300 | a3   |    3 |  300 | b2   |
|  4 |  400 | a4   | NULL | NULL | NULL |
|  5 |  500 | a5   |    5 |  500 | b3   |
+----+------+------+------+------+------+
5 rows in set (0.00 sec)
 
mysql> select a.*, b.* from a left join b on a.aa = b.ba;
+----+------+------+------+------+------+
| aa | ab   | ac   | ba   | bb   | bc   |
+----+------+------+------+------+------+
|  1 |  100 | a1   |    1 |  100 | b1   |
|  2 |  200 | a2   | NULL | NULL | NULL |
|  3 |  300 | a3   |    3 |  300 | b2   |
|  4 |  400 | a4   | NULL | NULL | NULL |
|  5 |  500 | a5   |    5 |  500 | b3   |
+----+------+------+------+------+------+
5 rows in set (0.00 sec)