1 학습목표


2 JOIN

JOIN

JOIN

2.1 MySQL JOIN

  • LEFT JOIN

    SELECT a.v1, b.w1 FROM a LEFT JOIN b ON a.k1 = b.k2;
  • RIGHT JOIN

    SELECT a.v1, b.w1 FROM a RIGHT JOIN b ON a.k1 = b.k2;
  • INNER JOIN

    SELECT a.v1, b.w1 FROM a INNER JOIN b ON a.k1 = b.k2;
  • OUTER JOIN

    SELECT a.v1, b.w1 FROM a FULL OUTER JOIN b ON a.k1 = b.k2;

2.2 Join 검색

  • Join은 여러 테이블에 저장된 데이터를 하나의 SQL 문으로 한번에 검색할 수 있는 기능
  • 이 조인기능은 현재 관계 DBMS를 사실상 표준으로 만드는데 결정적인 역할
  • 두 개 이상의 테이블을 조인하기 위해서는 왜래키(foreign key)가 적어도 하나 이상 있어야 함
  1. 조인문을 작성하는 순서
    1. SELECT 절에서 검색하고 하는 열 이름들을 명시
    2. FROM 절에서 SELECT 절에 지정된 열 이름들의 소속 테이블 이름을 명시
    3. FROM 절에 지정된 테이블 이름이 두 개 이상이면, WHERE 절에 조인 조건절을 명시
  2. 두개 이상의 테이블에서 열이름이 중복을 해결하기 위해서 테이블이름과 열이름을 반드시 점(.)으로 구분
    • 예) 두 테이블에 동일 컬럼명 id: 조인문에서는 professor.idstudent.id를 사용
  3. 테이블 이름을 AS문을 이용하여 다른 이름으로 변경 가능
    • FROM 절에서 테이블 이름에 별명을 지정한 후, 조인 조건절(where)에서 지정된 별명을 사용

2.3 테이블 명세서 예제

테이블명 컬럼명 데이터형식 NULL유무 기본키 외래키 FK-TABLE FK컬럼명
학과 번호 INTEGER NOT NULL 0
(DEPART) 이름 VARCHAR(50)
교수 번호 INTEGER NOT NULL 0
(PROFESSOR) 이름 VARCHAR(50)
학과번호 INTEGER NOT NULL 0 DEPART 번호
STUDENT 번호 INTEGER NOT NULL 0
(학생) 이름 VARCHAR(30)
주소 VARCHAR(50)
학년 INTEGER
INTEGER
몸무게 INTEGER
별명 VARCHAR(20)
별명이유 VAHRCHAR(50)
학과번호 INTEGER NOT NULL 0 DEPART 번호
CURRICULUM 번호 INTEGER NOT NULL 0
(과목) 이름 VARCHAR(30)
학점 INTEGER
교수번호 INTEGER NOT NULL 0 PROF 번호
REGIST 번호 INTEGER NOT NULL 0
(등록) 등록일 DATETIME
학생번호 INTEGER NOT NULL 0 STUDENT 번호
LECTURE 등록번호 INTEGER NOT NULL 0 0 REGIST 번호
(수강) 과목번호 INTEGER NOT NULL 0 LECTURE 번호
중간성적 DECIMAL(6,2)
기말성적 DECIMAL(6,2)
  • Join 검색 예제: 교수의 번호, 이름, 학과 이름을 출력

    SELECT p.id as 교수번호, p.name as 교수이름, d.name as 학과명
    FROM professor as p, depart as d
    where d.id = p.id;
    
    +----------+----------+-------------+
    | 교수번호 | 교수이름 | 학과명      |
    +----------+----------+-------------+
    |   1      | 김태길   | 국문과      |
    |   2      | 김봉남   | 연극영화과  |
    |   3      | 조정래   | 영문과      |
    |   4      | 이문열   | 의류학과    |
    |   5      | 안성기   | 전산학과    |
    |   6      | 장미희   | 철학과      |
    +----------+----------+-------------+

2.4 크로스 조인(full join or cross join)

t1          t2
+-------+  +---------+
i1  c1      i2  c2
+-------+  +---------+
1   a       2   c
2   b       3   b
3   c       4   a
+-------+  +---------+
SELECT t1.*, t2.* FROM t1,t2;
+------+------+------+------+
| i1   | c1   | i2   | c2   |
+------+------+------+------+
|    1 | a    |    2 | c    |
|    2 | b    |    2 | c    |
|    3 | c    |    2 | c    |
|    1 | a    |    3 | b    |
|    2 | b    |    3 | b    |
|    3 | c    |    3 | b    |
|    1 | a    |    4 | a    |
|    2 | b    |    4 | a    |
|    3 | c    |    4 | a    |
+------+------+------+------+

SELECT t1.*, t2.* FROM t1,t2 WHERE t1.i1=t2.i2;
+------+------+------+------+
| i1   | c1   | i2   | c2   |
+------+------+------+------+
|    2 | b    |    2 | c    |
|    3 | c    |    3 | b    |
+------+------+------+------+
  • 다음의 SQL문을 수행해 보고 위의 결과와 비교해 보시오.

    SELECT t1.*, t2.* FROM t1 JOIN t2 WHERE t1.i1=t2.i2;
    SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1=t2.i2;
    SELECT t1.*, t2.* FROM t1 CROSS JOIN t2 WHERE t1.i1=t2.i2;
    SELECT t1.*, t2.* FROM t1 CROSS JOIN t2 ON t1.i1=t2.i2;

2.5 외부조인(outer join)

SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i1=t2.i2;
+------+------+------+------+
| i1   | c1   | i2   | c2   |
+------+------+------+------+
|    1 | a    | NULL | NULL |
|    2 | b    |    2 | c    |
|    3 | c    |    3 | b    |
+------+------+------+------+

SELECT t1.*, t2.* FROM t1 RIGHT JOIN t2 ON t1.i1=t2.i2;
+------+------+------+------+
| i1   | c1   | i2   | c2   |
+------+------+------+------+
|    2 | b    |    2 | c    |
|    3 | c    |    3 | b    |
| NULL | NULL |    4 | a    |
+------+------+------+------+

2.6 내부조인(inner join)

SELECT * FROM (Artist INNER JOIN CD ON CD.ArtistID=Artist.ArtistID)
    WHERE Artist.ArtistID=22;

3 서브 쿼리

3.1 서브 쿼리(sub query, sub select)

  • 하나의 SQL문 처리 결과를 다른 SQL문에 전달하여 새로운 결과를 검색하는 기능
  • 이 경우 ORDER BY 절은 사용이 불가
  • 예: 학생 테이블에서 김태희 학생과 학년이 동일한 모든 학생의 이름, 키, 몸무게를 검색

    SELECT name AS "이름", weight AS "몸무게", 
           height AS "키", grade AS "학년"
      FROM student WHERE 
          grade=(SELECT grade FROM student WHERE name="김태희");

3.2 서브 쿼리(sub query, sub select)

  • 문과가 포함되는 학과에 소속된 모든 학생의 번호, 이름, 학년, 학과번호를 검색
  • 서브쿼리의 검색결과가 여러 행: IN, EXISTS 등 다중행 비교연산자를 사용

    SELECT id AS 학번, name as 이름, grade as 학년, did as 학과번호
    FROM student
    WHERE did IN
        (SELECT id FROM depart WHERE name LIKE '%문과%');

3.3 UNION

  1. t1의 i1과 t2의 i2의 합집합

    SELECT i1 AS i FROM t1 UNION SELECT i2 AS i FROM t2;
    +------+
    | i    |
    +------+
    |    1 |
    |    2 |
    |    3 |
    |    4 |
    +------+
  2. t1과 t2의 합집합

    SELECT * FROM t1 UNION SELECT * FROM t2;
    +------+------+
    | i1   | c1   |
    +------+------+
    |    1 | a    |
    |    2 | b    |
    |    3 | c    |
    |    2 | c    |
    |    3 | b    |
    |    4 | a    |
    +------+------+
  3. 여러 테이블의 합집합

    SELECT * FROM t3;
    +------+------+
    | i3   | c3   |
    +------+------+
    |    2 | e    |
    |    6 | a    |
    +------+------+
    2 rows in set (0.00 sec)
    
    SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT * FROM t3;
    +------+------+
    | i1   | c1   |
    +------+------+
    |    1 | a    |
    |    2 | b    |
    |    3 | c    |
    |    2 | c    |
    |    3 | b    |
    |    4 | a    |
    |    2 | e    |
    |    6 | a    |
    +------+------+
    SELECT * FROM t1 LIMIT 1
    UNION SELECT * FROM t2 LIMIT 1
    UNION SELECT * FROM t3 LIMIT 1;
    +------+------+
    | i1   | c1   |
    +------+------+
    |    1 | a    |
    +------+------+
    
    (SELECT * FROM t1 LIMIT 1)
    UNION (SELECT * FROM t2 LIMIT 1)
    UNION (SELECT * FROM t3 LIMIT 1);
    +------+------+
    | i1   | c1   |
    +------+------+
    |    1 | a    |
    |    2 | c    |
    |    2 | e    |
    +------+------+