조인(JOIN) 이란 두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어내는 것을 의미한다.
데이터베이스의 테이블은 중복과 공간 낭비를 피하고 데이터의 무결성을 위해서 여러 개의 테이블로 분리하여 저장한다. 그리고 분리된 테이블들은 서로 관계(Relation)을 맺고 있다. 이 테이블간의 관계를 통해 데이터를 검색하며, 일반적으로 공통된 키(예: 기본키와 외래키)를 사용한다.
1. INNER JOIN(내부 조인)
조인 중에서 가장 많이 사용되는 조인이다. 일반적으로 JOIN 이라고 하면 INNER JOIN을 지칭한다. INNER JOIN은 두 테이블에서 공통된 값이 일치하는 경우만 조회된다.
SELECT <컬럼명....> FROM <테이블1> [INNER] JOIN <테이블2> ON <조인될 조건> [WHERE 검색조건]
INNER JOIN은 JOIN 이라고 표기해도 INNER JOIN으로 인식한다.
구매 테이블에서 ‘JYP’라는 아이디를 가진 사람이 구매한 물건을 발송하기 위해 이름/주소/연락처 등의 데이터를 회원 테이블과 조인해서 검색하려면 다음과 같이 사용하면 된다.
USE sqlDB SELECT * FROM buyTb1 a JOIN userTb1 b ON a.userid = b.userid WHERE a.userid = 'JYP'
조인을 할 때는 어느 테이블의 컬럼인지를 테이블명.컬럼명(buyTb1.userId) 혹은 테이블별칭.컬럼명(a.userId)으로 컬럼이 속해있는 테이블을 지정해주어야 한다.
우선 구매 테이블의 userid 인 ‘JYP’를 추출하게 된다. 그리고 ‘JYP’와 같은 값을 회원 테이블의 userid 열에 검색한 후, ‘JYP’ 라는 아이디를 찾게 되면 구매 테이블과 회워 테이블의 두 행을 결합(JOIN) 한다.
만약 ‘WHERE buyTb1.userid = ’JYP’ 를 생략하게 되면, buyTb1의 모든 행에 대해 조인이 진행된다.
SELECT a.userid,b.name,a.prodName,b.addr,b.mobile1+b.mobile2 AS "전화번호" FROM buyTb1 a JOIN userTb1 b ON a.userid = b.userid
SELECT 절의 컬럼을 명시할 때도 테이블을 지정해야 한다.
이번에는 전체 회원들이 구매한 목록을 모두 출력해보자. 이전에는 구매 목록이 조회 목적이었기 때문에 기준 테이블이 구매 테이블이었다면, 이번에는 회원 테이블을 기준으로 조회한다.
SELECT a.userid,a.name,b.prodname,a.addr,a.addr,a.mobile1+a.mobile2 AS "전화번호" FROM userTb1 a JOIN buyTb1 b ON a.userid = b.userid ORDER BY a.userid
구매 테이블의 목록이 12건이므로 결과는 정상으로 조회됐다. 하지만 전체 회원을 조회하려고 했으나, 결과는 구매 이력이 없는 회원인 이승기, 김경호, 임재범, 윤종신, 조관우는 검색되지 않았다.
INNER JOIN은 두 테이블 간 일치하는 결과만 조회가 되기 때문에 전체 회원을 조회하려면 일치하지 않아도 조회가 되는 OUTER JOIN을 사용해야 한다.
2. OUTER JOIN(외부 조인)
OUTER JOIN은 두 개의 테이블을 결합할 때, 한쪽 테이블에 데이터가 없는 경우에도 결과를 반환한다. LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN 세 종류가 있으며, 일치하지 않는 데이터는 NULL 처리된다.
1. LEFT OUTER JOIN : 왼쪽에 있는 테이블이 기준 테이블로, 일치하는 값이 없어도 조회된다.
2. RIGHT OUTER JOIN : 오른쪽 테이블이 기준 테이블으로, 일치하는 값이 없어도 조회된다.
3. 양쪽의 모든 데이터를 반환한다.
SELECT <컬럼명...> FROM <테이블1> <LEFT | RIGHT | FULL> OUTER JOIN <테이블2> ON <조인 조건> [WHERE 검색 조건]
전체 회원의 구매 기록을 확인해보자. INNER JOIN과 다르게 구매 이력이 없는 회원도 조회가 되어야 한다.
SELECT a.userid,a.name,b.prodname,a.addr,a.addr,a.mobile1+a.mobile2 AS "전화번호" FROM userTb1 a LEFT OUTER JOIN buyTb1 b ON a.userid = b.userid ORDER BY a.userid
결과를 보면 검색 구매 이력이 없는 회원도 나타난 것을 확인할 수 있다. 구매 제품이 없기 때문에 NULL 로 표기된다.
SELECT a.userid,a.name,b.prodname,a.addr,a.addr,a.mobile1+a.mobile2 AS "전화번호" FROM buyTb1 b RIGHT OUTER JOIN userTb1 a ON a.userid = b.userid ORDER BY a.userid
RIGHT OUTER JOIN 을 사용하려면 회원 테이블과 구매 테이블의 위치만 바꿔주면 동일한 결과를 얻을 수 있다.
3. CROSS JOIN(상호 조인)
CROSS JOIN은 한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시키는 기능을 한다. 그래서 CROSS JOIN의 결과는 두 테이블의 행의 개수를 곱한 수가 된다.
CROSS JOIN은 컬럼의 일치를 확인하지 않기 때문에 ON 구문을 사용할 수 없다.
SELECT * FROM buyTb1 CROSS JOIN userTb1
결과를 확인했을 때, 회원 테이블은 총 10개, 구매 테이블은 12개의 데이터가 있기 때문에 곱인 120개의 결과가 조회가 되었다.
4. SELF JOIN(자체 조인)
SELF JOIN은 하나의 테이블이 자기 자신과 조인한다는 의미이다. 주로 직장 상사와 부하 직원의 관계같이, 계층형 관계를 조회할 때 사용한다.
SELECT a.emp as [부하직원], b.emp as [직속상관],b.department as [직속상관부서] FROM empTb1 a JOIN empTb1 b ON a.manager = b.emp
직원 테이블의 컬럼 중 상관에 대한 정보 컬럼이 있다. 상관 이름에 대한 컬럼과 직원 이름을 조인하면 직속 상관이 누군지를 확인할 수 있다.
5. UNION / UNION ALL / EXCEPT / INTERSECT
UNION / UNION ALL / EXCEPT / INTERSECT는 여러 쿼리의 결과 집합을 결합하거나 차집합을 구하는 데 사용되는 집합 연산자이다.
UNION : 두 쿼리의 결과를 결합하여 중복된 값을 제거하고 반환한다.
UNION ALL : 두 쿼리의 결과를 결합하며, 중복된 값은 제거하지 않고 그대로 합친다.
EXCEPT : 차집합. 첫번째 쿼리에서 두 번째 쿼리의 결과를 제외한 값을 출력한다.
INTERSECTION : 두 쿼리 결과 중 동일한 값만 출력한다.
중요한 점은 반드시 두 쿼리의 결과 컬럼 개수가 일치하고,, 서로 호환되는 데이터 형식어어야 한다.
만약 쿼리1의 컬럼이 INT 인데 쿼리2의 컬럼이 CHAR라면 오류가 발생할 것이다.
USE sqlDB CREATE TABLE stdTb1( stdName nvarchar(10), addr nchar(4) ) CREATE TABLE clubTb1( clubName nvarchar(10), roomNo nchar(4) ) INSERT INTO stdTb1 VALUES ('김범수','강남'), ('성시경','서울'),('조용필','경기') INSERT INTO clubTb1 VALUES ('수영','101호'), ('축구','102호')
두 개의 테이블을 만든 후 값을 입력한다.
SELECT * FROM stdTb1 UNION SELECT * FROM clubTb1
UNION 을 사용하면 두 쿼리 결과를 합친 값을 출력한다.
SELECT name,mobile1+mobile2 AS [전화번호] FROM userTb1 EXCEPT SELECT name,mobile1+mobile2 AS [전화번호] FROM userTb1 WHERE mobile1 IS NULL
EXCEPT는 첫 번째 쿼리에서 두 번째 쿼리의 결과를 제외한 값을 출력한다. 따라서 전화번호가 NULL인 값이 제외된 값이 출력된다.
SELECT name,mobile1+mobile2 AS [전화번호] FROM userTb1 INTERSECT SELECT name,mobile1+mobile2 AS [전화번호] FROM userTb1 WHERE mobile1 IS NULL
반면 INTERSECT 는 두 쿼리에서 일치하는 값만 조회되기 때문에 전화번호가 NULL인 값만 조회가 된다.
Share article