Computer Science/데이터베이스
PostgreSQL 문법 정리2 - AS, JOIN, UNION
ssun_bear
2023. 4. 14. 01:20
반응형
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_idFULL 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 YolandaLEFT 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 nullRIGHT 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.lengthJOIN 연습문제
- 캘리포니아에 살고 있는 고객의 이메일은 무엇인가?
SELECT district, email
FROM address
INNER JOIN customer ON address.address_id = customer.address_id
WHERE district = 'California'- 닉 월버그(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'- 테니스 코트(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- 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반응형