PostgreSQL 문법 정리2 - AS, JOIN, UNION

AS

• AS문은 열이나 결과에 별칭(alias)을 부여한다.
• SELECT column AS new_name FROM table
• SELECT SUM(column) AS new_name FROM table → 컬럼명이 new_name으로 출력된다
• AS연산자는 쿼리의 맨 마지막에 실행되기 때문에 WHERE, GROUP BY 호출, HAVING절 등에서는 쓸 수 없다.
• 정상 쿼리
  SELECT customer_id, sum(amount) as total_spent from payment group by customer_id having sum(amount) > 100
• 실행되지 않는 쿼리, 컬럼이 없다고 뜨면서 실행되지 않는다. AS는 맨 마지막에 실행되기 때문에 alias된 컬럼명을 인식하지 못한다.
  SELECT customer_id, sum(amount) as total_spent from payment group by customer_id having total_spent > 100
• 정상 쿼리
  SELECT customer_id, amount as new_name from payment where amount > 2
• 실행되지 않는 쿼리
  SELECT customer_id, amount as new_name from payment where new_name > 2

JOIN

• 여러 테이블의 레코드를 결합하여 하나의 열로 표현하는 것
• 결합된 테이블 중 하나에만 존재하는 정보를 처리하는 방식에 따라 분류한다.
• 하나의 쿼리에서 연달아 여러 개의 JOIN을 적용할 수 있다.
• ANSI 표준 SQL은 다음 다섯 가지 유형의 JOIN을 규정한다.
• INNER
• FULL OUTER
• LEFT OUTER
• RIGHT OUTER
• CROSS

예제 데이터 테이블, 이름은 모두 고유한 이름이라고 가정.

Registrations

reg_id	name
1	Andrew
2	Bob
3	Charlie
4	David

Logins

log_id	name
1	Xavier
2	Andrew
3	Yolanda
4	Bob

---

INNER JOIN

• 두 테이블 모두에서 일치하는 레코드를 조회하는 것
• 대칭적이기 때문에 테이블 순서를 바꿔도 결과는 같다.
• SELECT * FROM TableA INNER JOIN TableB ON TableA.col_match = TableB.col_match;
• SELECT * FROM TableB INNER JOIN TableA ON TableB.col_match = TableA.col_match;
• PostgreSQL에서는 그냥 JOIN이라고 쓰면 INNER JOIN으로 인식한다.

SELECT * FROM Registrations
INNER JOIN Logins
ON Registrations.name = Logins.name;

Results
reg_id  name       log_id   name
1       Andrew     2        Andrew     
3       Bob        4        Bob

• 양쪽에서 조회하려는 컬럼명이 같은 경우 어떤 테이블에 있는 컬럼인지 명시해줘야 한다.

SELECT payment_id, payment.customer_id, first_name
FROM payment
INNER JOIN customer
ON payment.customer_id = customer.customer_id

FULL OUTER JOIN

• 테이블A와 B의 가능한 모든 레코드를 조합한다.
• 서로 일치하는 데이터가 없으면 null로 표시된다.
• 대칭적이기 때문에 테이블 순서를 바꿔도 결과는 같다.
• SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.col_match = TableB.col_match;

SELECT * FROM Registrations
FULL OUTER JOIN Logins
ON Registrations.name = Logins.name;

Results
reg_id  name       log_id   name
1       Andrew     2        Andrew
2       Bob        4        Bob
3       Charlie    null     null
4       David      null     null
null    null       1        Xavier
null    null       3        Yolanda

• FULL OUTER JOIN에 WHERE문을 활용하여 양쪽 테이블에서 고유한 값을 조회할 수 있다.
• 대칭적이기 때문에 테이블 순서를 바꿔도 결과는 같다.
• INNER JOIN의 정반대인 결과가 나온다.
• SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.col_match = TableB.col_match WHERE TableA.id IS null OR TableB.id IS null;

SELECT * FROM Registrations
FULL OUTER JOIN Logins
ON Registrations.name = Logins.name
WHERE Registrations.reg_id IS null OR Logins.log_id IS null

Results
reg_id  name       log_id   name
3       Charlie    null     null
4       David      null     null
null    null       1        Xavier
null    null       3        Yolanda

LEFT OUTER JOIN (LEFT JOIN)

• 왼쪽 테이블에 있는 레코드 세트를 출력하고 오른쪽 테이블에 일치하는 데이터가 없으면 null로 출력한다.
• 벤 다이어그램으로 보듯이 대칭적이지 않다. 따라서 테이블 순서가 중요하다.
• SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.col_match = TableB.col_match;

SELECT * FROM Registrations
LEFT OUTER JOIN Logins
ON Registrations.name = Logins.name

Results
reg_id  name       log_id   name
1       Andrew     2        Andrew
2       Bob        4        Bob
3       Charlie    null     null
4       David      null     null

• 테이블A에만 있고 B에는 없는 행을 구하기 위해 LEFT OUTER JOIN을 이용하면서 WHERE절 조건을 활용하는 방법
• 대칭적이지 않기 때문에 테이블 순서가 중요하다.
• SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.col_match = TableB.col_match WHERE TableB.id IS NULL

SELECT * FROM Registrations
LEFT OUTER JOIN Logins
ON Registrations.name = Logins.name
WHERE Logins.log_id IS NULL

Results
reg_id  name       log_id   name
3       Charlie    null     null
4       David      null     null

RIGHT OUTER JOIN (RIGHT JOIN)

• 기본적으로 LEFT OUTER JOIN과 완전히 동일하지만 테이블이 서로 바뀐다는 점만 다르다.
• LEFT JOIN을 쓰면서 테이블 순서만 바꿔줘도 동일한 결과를 얻을 수 있다.
• SELECT * FROM TableA RIGHT OUTER JOIN TableB ON TableA.col_match = TableB.col_match;
• 테이블B에만 있는 데이터를 조회하기 위해 LEFT JOIN과 마찬가지로 WHERE를 활용할 수 있다.
• SELECT * FROM TableA RIGHT OUTER JOIN TableB ON TableA.col_match = TableB.col_match WHERE TableA.id IS null;

SELF JOIN

• 동일 테이블 사이의 조인, 테이블을 구별하기 위해 서로 다른 별칭을 사용해야 한다.

SELECT tableA.col, tableB.col 
FROM table AS tableA 
JOIN table AS tableB 
ON tableA.som_col = tableB.other_col

-- 같은 영화 테이블 안에서, 영화 별로 상영시간이 같은 영화의 목록 구하기
SELECT f1.title, f2.title, f1.length
FROM film AS f1
INNER JOIN film AS f2
ON f1.film_id != f2.film_id AND f1.length = f2.length

JOIN 연습문제

1. 캘리포니아에 살고 있는 고객의 이메일은 무엇인가?

SELECT district, email
FROM address
INNER JOIN customer ON address.address_id = customer.address_id
WHERE district = 'California'

1. 닉 월버그(Nick Wahlberg)라는 배우가 출연한 모든 영화 목록 찾기

SELECT title, first_name, last_name
FROM film_actor
INNER JOIN actor ON film_actor.actor_id = actor.actor_id
INNER JOIN film ON film_actor.film_id = film.film_id
WHERE first_name = 'Nick' AND last_name = 'Wahlberg'

1. 테니스 코트(facid 0, 1)의 2012년 9월 21일의 예약 시작시간 목록을 구하고 시작시간 오름차순으로 정렬하라

SELECT cd.bookings.starttime, cd.facilities.name
FROM cd.facilities
INNER JOIN cd.bookings ON cd.facilities.facid = cd.bookings.facid
WHERE cd.facilities.facid IN (0, 1) 
AND cd.bookings.starttime >= '2012-09-21'
AND cd.bookings.starttime <= '2012-09-22'
ORDER BY cd.bookings.starttime

1. David Farrell이란 회원의 예약 시작 시간을 구하라

SELECT cd.bookings.starttime
FROM cd.bookings
INNER JOIN cd.members
ON cd.bookings.memid = cd.members.memid
WHERE cd.members.firstname = 'David' AND surname = 'Farrell'

UNION

• UNION 연산자는 두 개 이상의 SELECT문의 결과 세트를 결합하기 위해 사용된다.
• JOIN과 UNION의 기본적인 차이는 UNION은 두 결과를 직접 붙인다는 것이다.
• SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
• SELECT문이 논리적이어야 하고 서로의 바로 위에 결과를 쌓을 수 있도록 열이 일치해야 한다.
• ORDER BY로 정렬할 수 있다.

예제 테이블

Sales_2021_Q1

name	amount
David	100
Claire	50

Sales_2021_Q2

name	amount
David	200
Claire	100

SELECT * FROM Sales2021_Q1 UNION SELECT * FROM Sales2021_Q2;

Results
name    amount
David   100
Claire  50
David   200
Claire  100