PostgreSQL AS, JOIN, UNION 정리

AS

• AS문은 컬럼이나 결과에 별칭(alias)을 부여한다.
• SELECT column AS new_name FROM table
• SELECT SUM(column) AS new_name FROM table → 컬럼명이 new_name으로 출력된다
• 별칭을 컬럼 명 다음에 쓰면 AS를 생략할 수 있다. 가독성이 나빠질 수 있으므로 주의해야 한다.
  SELECT column1 alias1, column2 alias2 FROM table;
• AS연산자는 쿼리의 맨 마지막에 실행되기 때문에 WHERE, GROUP BY, HAVING 등에서 별칭을 쓸 수 없다.
• 정상 쿼리
  SELECT customer_id, sum(amount) as total_spent FROM payment GROUP BY customer_id HAVING sum(amount) > 100
• 실행되지 않는 쿼리, 컬럼이 없다고 뜨면서 실행되지 않는다.
  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
• WHERE 절에서도 별칭을 사용하려면 서브쿼리나 HAVING절을 이용하면 된다.

SELECT *
FROM (
    SELECT column_name AS alias_name
    FROM table_name
) AS subquery
WHERE alias_name = 'value';

-- mysql에선 다음과 같이 별칭을 쓸 수 있다. 
SELECT column_name AS alias_name
FROM table_name
HAVING alias_name = 'value';

JOIN

• 여러 테이블의 레코드를 결합하여 하나의 열로 표현하는 것 이다.
• 하나의 쿼리에서 연달아 여러 개의 JOIN을 적용할 수 있다.
• FROM절에 2개 이상의 테이블이 열거되고, 열거된 테이블 사이에 조인 방식을 기술한다.
• ON 또는 WHERE절에는 각 테이블의 레코드들 간의 연관성을 기술한다.
• 일반적으로 조인에서는 기본키(PK)와 외래키(FK)를 사용하여 연관성을 표현한다.
• 결합된 테이블 중 하나에만 존재하는 정보를 처리하는 방식에 따라 분류한다.
• ANSI 표준 SQL은 다음 다섯 가지 유형(types)의 JOIN을 규정하고 있다.
  • INNER
  • FULL OUTER
  • LEFT OUTER
  • RIGHT OUTER
  • CROSS
• OUTER를 쓰지 않고 JOIN만 쓰면 내부(INNER) 조인이라고 간주한다.
• JOIN 연산에 대한 조건문(condition)은 다음과 같다.
  • NATURAL
  • ON
  • USING

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

Registrations
reg_id	name
1	Andrew
2	Bob
3	Charlie
4	David

Logins
log_id	name
1	Xavier
2	Andrew
3	Yolanda
4	Bob

---

NATURAL JOIN(자연 조인)

• 자연 조인은 두 개 이상의 테이블 간에 공통된 열 이름을 기반으로 자동으로 조인하는 방식이다.
• 조인 대상 테이블들이 같은 이름의 컬럼을 갖고 있다면 이 컬럼에 해당하는 값이 같은 레코드 쌍을 자동으로 결합하기 때문에 조인 조건을 별도로 기술하지 않아도 자연 조인 연산이 가능하다.
• 그러나 조건을 좀 더 명확하게 하기 위해 ON, WHERE, USING등을 활용할 수도 있다.
• 자연 조인(NATURAL)은 조인의 유형이 아니라 조건이기 때문에 내부(INNER)조인과 외부(OUTER)조인 모두에 활용할 수 있다.

SELECT t1.col1, t1.col2, t2.col1, t2.col2
FROM table1 AS t1 NATURAL JOIN table2 AS t2;

-- 학생의 학생이름, 나이, 성별 그리고 학생이 전공한 학과의 학과이름, 이수학점 조회
SELECT A.학생이름, A.나이, B.학과이름, B.이수학점
FROM 학생 A NATURAL JOIN 전공 B

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으로 인식한다.
• 일반적으로 ON절에서는 조인 조건을 기술하고 WHERE절은 조건에 맞는 레코드만 선택하기 사용된다.
• Oracle의 내부 조인 구문 형식은 다음과 같다. 이 문법은 SQL 표준은 아니지만 대부분의 RDBMS에서 지원한다.

SELECT * 
FROM TABLE_A A, TABLE_B B 
WHERE A.COLUMN = B.COLUMN;

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

-- 나이가 30세 이상인 학생과 그 학생들이 소유한 계좌 테이블 조회
-- 학생.*와 계좌.*는 각각 학생 테이블과 계좌 테이블의 모든 열을 의미함 
SELECT 학생.*, 계좌.*
FROM 학생 INNER JOIN 계좌
ON 학생.학생번호 = 계좌.학생번호
WHERE 학생.나이 >= 30

-- 위의 쿼리를 Oracle JOIN 문법으로 변환하고 학생.학생이름, 계좌.잔액만 선택해서 조회
SELECT 학생.학생이름, 계좌.잔액
FROM 학생, 계좌
WHERE 학생.나이 >= 30 AND 학생.학생번호 = 계좌.학생번호;

OUTER JOIN(외부 조인)

• 외부 조인은 두 개 이상의 테이블 간에서 특정 조건을 기준으로 행을 매칭하여 조인하는 것을 의미한다.
• 외부 조인은 내부 조인과 동일하게 조건에 맞는 레코드를 결합시키면서도 조건에 맞지 않는 레코드도 결과에 포함시킬 수 있다.
• JOIN절을 기준으로 조건 불일치 레코드를 결과에 포함시키는 주체가 되는 테이블의 위치에 따라 세 가지 종류로 구분된다.
  • 완전 외부 조인(Full Outer Join): 양쪽 테이블의 모든 행을 포함하고, 양쪽 테이블 중 어느 한쪽에 매칭되는 행이 있으면 매칭 결과를 반환하고, 양쪽 테이블 모두에 매칭되는 행이 없으면 NULL 값을 반환한다.
  • 왼쪽 외부 조인(Left Outer Join): 왼쪽 테이블의 모든 행을 포함하고, 오른쪽 테이블과 매칭되는 행이 있으면 매칭 결과를 반환하고, 매칭되는 행이 없으면 NULL 값을 반환한다.
  • 오른쪽 외부 조인(Right Outer Join): 오른쪽 테이블의 모든 행을 포함하고, 왼쪽 테이블과 매칭되는 행이 있으면 매칭 결과를 반환하고, 매칭되는 행이 없으면 NULL 값을 반환한다.

FULL OUTER JOIN(완전 외부 조인)

• 테이블A와 B의 가능한 모든 레코드를 조합한다.
• 서로 일치하는 데이터가 없으면 null로 표시된다.
• 대칭적이기 때문에 테이블 순서를 바꿔도 결과는 같다.
• MySQL은 완전 외부 조인을 지원하지 않는다. 대신 UNION ALL연산을 사용할 수 있다.

-- 구문 형식
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

--mysql에서 UNION ALL
SELECT employee_id, employee_name FROM employees
UNION ALL
SELECT customer_id, customer_name FROM customers;

• FULL OUTER JOIN에 WHERE A.key IS NULL OR B.key IS NULL조건을 활용하여 양쪽 테이블에서 고유한 값을 조회할 수 있다.
• 대칭적이기 때문에 테이블 순서를 바꿔도 결과는 같다.
• 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

-- 학생의 학생번호, 이름과 그 학생이 수강신청한 과목의 과목코드, 신청시간 조회
-- 수강신청을 하지 않은 학생도 조회 됨(수강 테이블의 값이 null), 과목코드 기준 오름차순 정렬
SELECT A.학생이름, A.나이, B.과목코드, B.신청시각
FROM 학생 A LEFT OUTER JOIN 수강 B
ON A.학생번호 = B.학생번호
ORDER BY 과목코드 ASC

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

-- 구문 형식
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과 같은 방식으로 조인한다. 조인하는 테이블의 순서만 다르다.
• SELECT * FROM TableA RIGHT OUTER JOIN TableB ON TableA.col_match = TableB.col_match;

• 테이블B에만 있는 데이터를 조회하기 위해 LEFT OUTER JOIN과 마찬가지로 WHERE A.key IS NULL 조건을 활용할 수 있다.
• SELECT * FROM TableA RIGHT OUTER JOIN TableB ON TableA.col_match = TableB.col_match WHERE TableA.id IS NULL;

SELF JOIN(셀프 조인)

• 셀프 조인은 동일한 테이블에서의 조인이다. 컬럼을 구별하기 위해 테이블에 서로 다른 별칭을 사용해야 한다.
• 셀프 조인을 위한 SELF JOIN이라는 예약어는 없으며 동일한 테이블에서 조인하면 셀프 조인이 된다.
• 조인 수행 방식에 따라 INNER 또는 OUTER를 쓸 수 있다.

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

-- 과목의 과목코드, 과목명 그리고 그 과목의 선수과목의 과목코드, 과목명을 조회
-- 선수과목이 없는 과목도 조회
SELECT B.과목명, B.과목코드, A.과목명 AS 선수과목명, A.과목코드 AS 선수과목코드
FROM 과목 A RIGHT OUTER JOIN 과목 B
ON A.과목코드 = B.선수과목

CROSS JOIN(크로스 조인)

• 크로스 조인이란 조인하는 테이블의 레코드를 결합하는 조건 없이 가능한 모든 조합으로 결합하는 것이다.
• 관계 대수의 카티션 프로덕트(Cartesian Product) 연산과 같다.
• WHERE 절로 결합 조건을 지정할 수도 있다.

SELECT column1, column2
FROM table1
CROSS JOIN table2
WHERE condition

-- 학생 테이블에서 나이가 30세 이상인 학생의 레코드와 그 학생들의 계좌 레코드를 결합
SELECT 학생.*, 계좌.*
FROM 학생 CROSS JOIN 계좌
WHERE 학생.나이 >= 30

JOIN 연습문제

1. 캘리포니아에 살고 있는 고객의 이메일 찾기

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

2. 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'

3. 테니스 코트(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

4. 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;
• UNION의 대상이 되는 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

참고자료

• https://www.udemy.com/course/best-sql-2022/
• https://sql-joins.leopard.in.ua/