[쉽게 쓴 SQL] INTERSECT

[연산] 두 집합의 교집합을 보고 싶다

 

교집합? 이전에 했던 INNER JOIN과 같지 않은가? 실제 쓰임에서는 INNER JOIN 과 같아 잘 안쓴다. 

실습 세팅

CREATE TABLE employees 
(
	employee_id serial PRIMARY KEY
	,employee_name varchar(255) NOT null
);

CREATE TABLE keys
(
	employee_id int PRIMARY KEY
	,effective_date date NOT NULL
	,FOREIGN key(employee_id)
		REFERENCES employees(employee_id)
);

CREATE TABLE hipos
( 
	employee_id int PRIMARY KEY
	,effective_date date NOT NULL 
	,FOREIGN KEY (employee_id)
		REFERENCES employees (employee_id)
);

INSERT INTO employees (employee_name)
VALUES 
('jouce edwards')
,('diane collins')
,('alice stewert')
,('julie sanchesz')
,('heather morris')
,('tersa rogers')
,('Doris REed')
,('glora cook')
,('evelyn morgan')
,('jean bell')
;

COMMIT;

INSERT INTO keys
VALUES 
(1,'2002-02-01')
,(2,'2001-06-01')
,(5,'2002-01-02')
,(7,'2005-06-01')
;

COMMIT; 

INSERT INTO hipos
VALUES
(9,'2000-01-01')
,(2,'2002-06-01')
,(5,'2006-06-01')
,(10,'2005-06-01');

COMMIT;

SELECT * FROM employees;

SELECT * FROM keys;

SELECT * FROM hipos;

 

key와 hipo에 공통으로 포함되는 직원을 뽑고 싶다

SELECT 
	employee_id
FROM 
	keys
INTERSECT
SELECT
	employee_id 
FROM 	
	hipos;

이것이 INNER JOIN으로 하고 실행하면,

SELECT
	a.employee_id
FROM
	keys a,
	hipos b
WHERE
	a.employee_id = b.employee_id;

결과가 같다

역시 이렇게 작성해도 동일하다

SELECT
	a.employee_id
FROM
	keys a,
INNER JOIN hipos b 
	ON (a.employee_id = b.employee_id);