ABOUT ME

    -

    Today
    -
    Yesterday
    -
    Total
    -
    • [SQL] join에 대하여 (inner join, left join, self join, full outer join, cross join, natural join)
      database/sql 2021. 5. 25. 02:50

      Postgre SQL 은 점차 사용성이 확장되는 중입니다. 또한 Database 공부시 자주 보게 되는 예제라 포스팅하게 되었습니다. 앞으로 실습과정 및 공부는 이 예제를 중심으로 진행할 예정입니다. 

      목표 : join의 종류를 학습하고 예제를 통해 

      이 페이지를 통해 아래 정보를 확인하실 수 있습니다. 

      1. inner join
      2. left, right join
      3. self join
      4. full outer join
      5. cross join
      6. natural join

      1. inner join

      inner join

      형식은 아래와 같습니다. 

      SELECT
	pka,
	c1,
	pkb,
	c2
FROM
	A
INNER JOIN B ON pka = fka;

      << 예시 >>

      방문객들과 지불금액의 연관관계를 확인하는 sql 쿼리문입니다. 

       

      1. 먼저 아래와 같이 ERD에서 customer와 payment에 어떤 key를 이용하여 연결되어 있는지 확인합니다.

      2. 찾은 key를 이용하여 inner join 쿼리를 작성합니다.

      SELECT
	customer.customer_id,
	first_name,
	last_name,
	amount,
	payment_date
FROM
	customer
INNER JOIN payment 
    ON payment.customer_id = customer.customer_id
ORDER BY payment_date;

      위에서 보는것과 같이 고객의 id를 이용하여 payment 테이블과 inner join을 통해 각 지불 기록별 사용자 정보를 확인할 수 있는 결괏값을 얻을 수 있습니다. 

      이처럼 다른 테이블끼리 inner join 을 통해 의미 있는 정보를 얻을 수 있습니다. 

       

      << 확장 >>

      그렇다면 한단계 확장해서 생각해 보겠습니다. 

      영화별 카테고리에 들어있는 name을 통해 정보를 알고 싶다면 어떻게 해야 할까요?

       

      1. 먼저 아래와 같이 ERD에서 film과 category에 어떤 key를 이용하여 연결되어 있는지 확인합니다. 

      2. 연관된 키를 통해 inner join 쿼리를 작성합니다.

      SELECT
	f.title, c.category_name 
FROM
	film f 
INNER JOIN film_category fc 
    ON f.film_id = fc.film_id 
INNER JOIN categories c 
    ON c.category_id = fc.category_id ;

      이처럼 간단하게 두 번 연속으로 inner join을 하면 쉽게 해결할 수 있습니다. 

      left , right outer join

      left outer join

      형식은 아래와 같습니다. 

      SELECT
	pka,
	c1,
	pkb,
	c2
FROM
	A
LEFT(RIGHT) JOIN B ON pka = fka;

      그렇다면 film과 inventory를 이용하여 inventory에 할당되지 않은 값을 찾아보는 예제를 수행하겠습니다. 

       

      1. 먼저 아래와 같이 ERD에서 film과 inventory에 어떤 key를 이용하여 연결되어 있는지 확인합니다.

      2. 연관 key릉 통해 left join 쿼리를 작성하면 됩니다.

      SELECT
	film.film_id,
	title,
	inventory_id
FROM
	film
LEFT JOIN inventory 
    ON inventory.film_id = film.film_id
ORDER BY title;

       

      << 확장 >>

      그렇다면 한 단계 더 확장해서 아래와 같은 결과를 얻고자 한다면 쿼리를 어떻게 수정해야 될까요?

       

      차집합

      바로 is null을 활용하면 해결할 수 있습니다. 

      오른쪽 원에 해당하는 inventory_id가 서로 맵핑이 안 되는 경우 NULL이라는 결괏값을 출력하므로

      아래와 같이 쿼리를 작성하면 차집합의 결과를 얻을 수 있습니다. 

      SELECT
	film.film_id,
	film.title,
	inventory_id
FROM
	film
LEFT JOIN inventory 
   ON inventory.film_id = film.film_id
WHERE inventory.film_id IS NULL
ORDER BY title;

      무려 42개의 데이터가 inventory id 없이 생성되었음을 확인할 수 있습니다.

      self join 

      필자는 이 부분이 가장 유용하게 사용될 것이라고 생각합니다. 

      self join의 가장 좋은 예는 회사에서 '직원'과 '직원의 사수' 관계에서 확인하는 것이 가장 이해가 빨랐습니다.  

      DVD rental에서는 '직원'과 '직원의 사수' 관계와 같은 예시가 없음으로 다른 예제로 대체하겠습니다. 

      예제 등록 코드 

      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
);
INSERT INTO employee (
	employee_id,
	first_name,
	last_name,
	manager_id
)
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);

      해당 테이블을 확인하시면 아래와 같습니다. 

      select *
from employee e ;

      여기서 사장인 Windy Hays는 manager_id가 없는 NULL입니다. 

      여기서 각 직원별 이름과 사수를 출력하려면 아래와 같이 self join을 통해 쿼리를 작성할 수 있습니다. 

       

      inner join 을 사용한 쿼리

      SELECT
    e.first_name || ' ' || e.last_name employee,
    m .first_name || ' ' || m .last_name manager
FROM
    employee e
INNER JOIN employee m ON m .employee_id = e.manager_id
ORDER BY manager;

      아.. 사장님이 빠졌군요...

      그럼 left join으로 사장님도 포함시키도록 하겠습니다. 

       

      SELECT
    e.first_name || ' ' || e.last_name employee,
    m .first_name || ' ' || m .last_name manager
FROM
    employee e
LEFT JOIN employee m ON m .employee_id = e.manager_id
ORDER BY manager;

       

      full outer join

      full outer join

      SELECT * FROM A
FULL [OUTER] JOIN B on A.id = B.id;

      먼저 실습을 위해 아래 코드를 입력하여 table을 생성해 줍니다. 

      DROP TABLE IF EXISTS departments;
DROP TABLE IF EXISTS employees;

CREATE TABLE departments (
	department_id serial PRIMARY KEY,
	department_name VARCHAR (255) NOT NULL
);

CREATE TABLE employees (
	employee_id serial PRIMARY KEY,
	employee_name VARCHAR (255),
	department_id INTEGER
);
      INSERT INTO departments (department_name)
VALUES
	('Sales'),
	('Marketing'),
	('HR'),
	('IT'),
	('Production');

INSERT INTO employees (
	employee_name,
	department_id
)
VALUES
	('Bette Nicholson', 1),
	('Christian Gable', 1),
	('Joe Swank', 2),
	('Fred Costner', 3),
	('Sandra Kilmer', 4),
	('Julia Mcqueen', NULL);

      그렇다면 실습을 통해 알아보겠습니다. 

      employees table과 departments의 함께 연관된 모든 데이터를 확인할 수 있습니다. 

      일단 아래와 같이 쿼리를 작성한 결과를 보겠습니다. 

      SELECT
	employee_name,
	department_name
FROM
	employees e
FULL OUTER JOIN departments d 
        ON d.department_id = e.department_id;

      << 확장 >>

      full outer join으로 left outer join, right outer join 만들기를 해보겠습니다. 

      먼저 full outer join으로 만들고 'IS NULL'로 left outer join, right outer join을 만들면 됩니다.

      SELECT
	employee_name,
	department_name
FROM
	employees e
FULL OUTER JOIN departments d 
        ON d.department_id = e.department_id
WHERE
	employee_name IS NULL;

      SELECT
	employee_name,
	department_name
FROM
	employees e
FULL OUTER JOIN departments d ON d.department_id = e.department_id
WHERE
	department_name IS NULL;

      Cross Join

      cross join은 확장 데이터를 

      실습환경 구성 

      DROP TABLE IF EXISTS T1;
CREATE TABLE T1 (label CHAR(1) PRIMARY KEY);

DROP TABLE IF EXISTS T2;
CREATE TABLE T2 (score INT PRIMARY KEY);

INSERT INTO T1 (label)
VALUES
	('A'),
	('B');

INSERT INTO T2 (score)
VALUES
	(1),
	(2),
	(3);

      join 이 없다는 것이 특징입니다.

      SELECT *
FROM T1
CROSS JOIN T2;

      아래 코드와 동일합니다. 

      SELECT *
FROM T1, T2;

      << 확장 >>

      case when 사용법에 대해 익혀두도록 하겠습니다. 

      SELECT 
	case when label ='A' then sum(score)
		 when label ='B' then sum(score) * -1
	else 0
	end 
FROM T1, T2
group by label ;

      natural join 

      실무에서 많이 사용되지 않다고 합니다

      자신의 의도와는 다른 경우가 발생할 수 있습니다.

       

       

      사용법

      SELECT select_list
FROM T1
NATURAL [INNER, LEFT, RIGHT] JOIN T2;

      실습 예제

      DROP TABLE IF EXISTS categories;
CREATE TABLE categories (
	category_id serial PRIMARY KEY,
	category_name VARCHAR (255) NOT NULL
);

DROP TABLE IF EXISTS products;
CREATE TABLE products (
	product_id serial PRIMARY KEY,
	product_name VARCHAR (255) NOT NULL,
	category_id INT NOT NULL,
	FOREIGN KEY (category_id) REFERENCES categories (category_id)
);


INSERT INTO categories (category_name)
VALUES
	('Smart Phone'),
	('Laptop'),
	('Tablet');

INSERT INTO products (product_name, category_id)
VALUES
	('iPhone', 1),
	('Samsung Galaxy', 1),
	('HP Elite', 2),
	('Lenovo Thinkpad', 2),
	('iPad', 3),
	('Kindle Fire', 3);

       

      문제점. 

      자신의 의도와는 다른 경우가 발생할 수 있습니다.

      아래와 같이 아무런 결과가 도출되지 않습니다.

      SELECT * 
FROM city
NATURAL JOIN country;

      innerjoin 사용이 안전하다. 

      댓글

    Designed by Tistory.

    티스토리툴바