SQL) SELF JOIN, CONCAT(문자열 합치기)

 본 게시글은 Datacamp의 Introduction to SQL 코스와 패스트캠퍼스의 SQL/DB 올인원 패키지 강좌를 수강하며 제가 기억할 수 있도록 작성한 것으로, 아주 기초적인 내용으로 구성되어 있습니다.

 

---

SELF JOIN

 동일 테이블 사이의 조인. 즉, 하나의 테이블이 특정 컬럼을 기준으로 자기 자신과 조인하는 것을 말한다.

 

ex. country_code 컬럼을 기준으로 populations 테이블을 셀프 조인하여 각각을 p1, p2라고 명명하고, p1의 country_code, size(두 컬럼의 공통 컬럼으로 size2010, size2015의 두 개의 컬럼으로 표현할 것)을 조회

SELECT p1.country_code, p1.size size2010, p2.size size2015
FROM populations p1
JOIN populations p2
USING(country_code);

 

ex. employee 테이블에서 자기자신의 employee_id 컬럼을 참조하는 manager_id(FK)컬럼 구성

1) employee 테이블의 CREATE문은 다음과 같다.

CREATE TABLE employee
(
	employee_id int PRIMARY KEY,
	first_name varchar(255) NOT NULL,
	last_name varchar(255) NOT NULL,
	manager_id int,
	FOREIGN KEY (manager_id)
	REFERENCES employee (employee_id)
	ON DELETE cascade
);

 

2) 해당 테이블을 생성 후 아래와 같이 데이터들을 입력한다.

INSERT INTO employee 
VALUES
	(1, 'Windy', 'Hays', NULL),
	(2, 'Ava', 'Christensen', 1),
	(3, 'Hassan', 'Conner', 1),
	(4, 'Anna', 'Reeves', 2),
	(5, 'Sau', 'Norman', 2),
	(6, 'Kelsie', 'Hays', 3),
	(7, 'Tory', 'Goff', 3),
	(8, 'Salley', 'Lester', 3);

 

3) 이 데이터들은 결국 아래와 같은 구조의 데이터를 의미한다.

각 종업원들은 매니저를 가진다. 최상위의 종업원은 매니저를 갖지 않는다.

 

4) SELF INNER JOIN - 각 직원의 매니저(상위 관리자) 출력(최고 관리자인 windy Hays는 조회되지 않는다.)

SELECT
	concat(e1.first_name, ' ',  e1.last_name) employee,
	concat(e2.first_name, ' ',  e2.last_name) manager
FROM employee e1
JOIN employee e2
ON e1.manager_id = e2.employee_id
ORDER BY manager;

 

 

5) SELF LEFT OUTER JOIN - 각 직원의 매니저(상위 관리자) 출력(최고 관리자도 조회된다.)

SELECT
	concat(e1.first_name, ' ',  e1.last_name) employee,
	concat(e2.first_name, ' ',  e2.last_name) manager
FROM employee e1
LEFT JOIN employee e2
ON e1.manager_id = e2.employee_id
ORDER BY manager;

 

6) SELF RIGHT OUTER JOIN - 각 직원의 하위 직원 출력(가독성을 위해 컬럼 순서 변경)

SELECT
	concat(e2.first_name, ' ',  e2.last_name) manager,
	concat(e1.first_name, ' ',  e1.last_name) employee
FROM employee e1
RIGHT JOIN employee e2
ON e1.manager_id = e2.employee_id
ORDER BY manager;

 

 위의 쿼리는 다음과 같은 결과를 출력한다.

가장 말단의 종업원들은 employee가 조회되지 않음을 볼 수 있다.

 

---

SELF JOIN시 부정형 조건 사용하기

 JOIN을 할 때 항상 '='를 이용해 두 컬럼값이 일치하도록 할 수만 있는 것은 아니다. 컬럼값이 다름을 조건으로 삼을 때에는 아래와 같이 작성할 수 있다. 해당 쿼리를 실행하면, film 테이블을 셀프 조인하되, 서로 상영 시간은 같지만 제목이 다른 영화들을 맵핑시킨 결과를 출력한다.

SELECT 
	f1.title, f2.title, f1.length 
FROM film f1
JOIN film f2
ON f1.film_id <> f2.film_id 
AND f1.length = f2.length;

 

 

---

문자열 합치기(CONCAT, ||)

 * 문자열을 합치기 위해서는 CONCAT 함수를 사용할 수도 있지만, 다음과 같이 문자열을 합칠 수도 있다. (PostgreSQL뿐만 아니라 Oracle에서도 제공하는 기능이다.)

-- concat() 함수 사용
concat(e1.first_name, ' ', e1.last_name)

-- '||' 사용
e1.first_name || ' ' || e1.last_name employee

 

주의할 점 (NULL값 처리)

- CONCAT 함수는 NULL을 인식하지 않기 때문에 합치려는 값들 중 NULL이 속해있을 경우에는 이를 무시하고 다른 문자열들을 합친다.

- Vertical Bar(||)는 NULL을 인식하기 때문에, 합치려는 값들 중에 하나라도 NULL이 속해있으면 NULL을 리턴한다.