Code Compass
[sql] JOIN, AS, INNER JOIN, FULL OUTER JOIN, LEFT OUTER JOIN, RIGHT JOIN, UNION

[sql] JOIN, AS, INNER JOIN, FULL OUTER JOIN, LEFT OUTER JOIN, RIGHT JOIN, UNION

KiwiChip's photo
KiwiChip
·

7 min read

✅ AS – 컬럼 또는 테이블에 별칭 부여

SELECT name AS employee_name FROM employees;
SELECT SUM(amount) AS net_revenue FROM payment;
-- 특히 열에 함수를 적용할 때 유용

  • name 컬럼을 employee_name이라는 별칭으로 표시

  • AS는 무조건 결과나 이름을 바꾸려는 열이나 함수 뒤에 와야 함

  • 특히 열에 함수를 적용할 때 유용

  • AS 연산자는 쿼리의 맨 마지막에 실행

    • total_spent는 output의 맨 마지막에 실행되기 때문에 WHERE, GROUP BY, HAVING절 등에서는 별칭을 사용할 수 없음

      올바른 예시:

      에러 예시:

✅ JOIN – 두 개 이상의 테이블 결합

SELECT employees.name, departments.department_name 
FROM employees 
JOIN departments ON employees.department_id = departments.id;
  • employees 테이블과 departments 테이블을 department_id를 기준으로 결합

✅ INNER JOIN(or JOIN) – 두 테이블을 모두 충족하는 데이터만 결합

SQL INNER JOIN Operation - Tutorial Republic

SELECT *
FROM TableA --TableB와
INNER JOIN TableB --TableA로 바꿔도 상관 없음
ON TableA.col_match = TableB.col_match; -- 결합 조건

  • 두 테이블 모두에 있는 행만 가져옴

  • 벤타이어그램이 대칭이라 두 테이블의 위치가 달라져도 상관 없음

Registrations 테이블(1, 2열)과 Logins 테이블(3, 4열)

reg_idnamelog_idname
1Andrew1Xavier
2Bob2Andrew
3Charlie3Yolanda
4David4Bob

  • 위 테이블(왼쪽, Registrations / 오른쪽, Logins)에는 양쪽 테이블에 모두 있는 행(Andrew, Bob)이 있음

  • INNER JOIN을 수행하려면 어떤 열을 참조해야할 지 결정해야 함(Registrations.name = Logins.name 부분)

INNER JOIN 결과 1:

SELECT * FROM Registrations
INNER JOIN Logins
ON Registrations.name = Logins.name
  • Registrations 결과에서 모든 열을 선택하고 name 열을 기준으로 Logins에 INNER JOIN을 한 결과
reg_idnamelog_idname
1Andrew2Andrew
2Bob4Bob

INNER JOIN 결과 2:

SELECT reg_id, Logins.name, log_id
FROM Registrations
INNER JOIN Logins
ON Registrations.name = Logins.name -- 결합 조건
reg_idnamelog_id
1Andrew2
2Bob4

  • SELECT문에서 Logins.name으로 적는 이유

    • name이 두 테이블 모두에 존재하기 때문

    • 중복되지 않는 컬럼 이름이라면 어떤 테이블에 있는 것인지 명시할 필요 없이 그대로 쓰면 됨

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

  • PostgreSQL에서는 JOIN이라 적으면 INNER JOIN이 실행됨

✅ FULL (OUTER) JOIN – 모든 데이터 결합 (일치 여부 무관)

SQL Full Join Illustration

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.col_match = TableB.col_match;

  • 두 테이블에서 일치하는 데이터 + 일치하지 않는 데이터까지 모두 반환
    (지원하지 않는 DBMS도 있음, UNION을 사용해 구현 가능)

  • TableA, TableB의 순서가 바뀌어도 완전한 대칭이라 상관 없음

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

FULL (OUTER) JOIN 결과

reg_idnamelog_idname
1Andrew2Andrew
2Bob4Bob
3Charlienullnull
4Davidnullnull
nullnull1Xavier
nullnull3Yolanda
  • 전부 선택한 후, 한 테이블에만 존재하는 행에는 null 값을 입력

FULL (OUTER) JOIN with WHERE

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.col_match = TableB.col_match
WHERE TableA.id IS null OR TableB.id IS null;

  • FULL OUTER JOIN에 WHERE문으로 자격을 부여하는 것

  • 두 테이블에 모두 나와있지 않은 행을 구하는 것 (INNER JOIN과 반대 개념)

  • INNER JOIN과 마찬가지로 테이블의 위치는 서로 바뀌어도 결과는 같음

  • 쓸 수 있는 상황:

    • 새로운 개인정보 보호정책이 시행되어, 고객에게 첨부되지 않은 결제 정보나 결제에 첨부되지 않은 고객 정보를 가져오지 않도록 지정하려고 함

    • 모든 결제 정보는 현재 고객과 관련되어 있고, 모든 고객 정보가 과거 결제 정보와 관련되어 있는지 확인하려고 함

    • 비디오 가게라면, 이 비디오 가게에서 실제로 아무것도 빌리지 않은 사람의 고객 ID는 보유하려고 하지 않으려고 하는 상황

        SELECT * FROM customer
  FULL OUTER JOIN payment
  ON customer.customer_id = payment.customer_id -- customer 테이블과 payment 테이블을 결합하여 customer_id를 결합
  WHERE customer.customer_id IS null -- 결합한 테이블을 필터링해서 customer  테이블에만 고유한 행 찾기
  OR payment.payment_id IS null

✅ LEFT (OUTER) JOIN – 왼쪽 테이블의 모든 데이터 포함

SQL Left Join Illustration

SELECT * FROM TableA -- 왼쪽 테이블
LEFT OUTER JOIN TableB -- 오른쪽 테이블
ON TableA.col_match = TableB.col_match;

  • TableA(왼쪽) 테이블의 모든 데이터 반환, TableB(오른쪽)에 일치하는 값이 없으면 NULL

  • TableB에만 속하는 정보는 가져오지 않음

  • 벤다이어그램이 더 이상 대칭이 아니므로, 테이블의 순서가 중요

  • LEFT OUTER JOIN은 LEFT JOIN으로 줄여 작성 가능

  • LEFT (OUTER) JOIN 결과

reg_idnamelog_idname
1Andrew1Xavier
2Bob2Andrew
3Charlienullnull
4Davidnullnull
  • TableB(Login)에만 속하는 Xavier와 Yolanda는 표시되지 않음

LEFT(TableA, Registration)에만 속하는 정보만 표시(WHERE 사용)

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.col_match = TableB.col_match
WHERE TableB.id IS null;

결과

reg_idnamelog_idname
3Charlienullnull
4Davidnullnull
  • TableA(Registration)에만 해당되는 정보 결과를 보여줌

✅ RIGHT (OUTER) JOIN – 오른쪽 테이블의 모든 데이터 포함

SQL Right Join Illustration

SELECT * FROM TableA
RIGHT OUTER JOIN TableB
ON TableA.col_match = TableB.col_match;

  • TableB의 모든 데이터 반환, TableA에 일치하는 값이 없으면 NULL

  • TableB에만 있거나 TableB와 TableA 모두에 있는 행을 반환함(TableA에만 있는 행은 반환 X)

  • 벤다이어그램이 더 이상 대칭이 아니므로, 테이블의 순서가 중요

  • RIGHT OUTER JOIN은 RIGHT JOIN으로 줄여 작성 가능

✅ UNION – 두 개 이상의 SELECT 결과 합치기

SELECT column_name FROM table1
UNION 
SELECT column_name FROM table2;

  • table1과 table2에서 중복을 제거한 column_name 목록 반환

  • 두 테이블의 열이 일치해야 함

  • 원할 경우, UNION을 여러 번 불러서 여러 결과를 붙일 수 있음

  • 그 외에도 ORDER BY를 이용해서 기준을 정해 정렬 및 분류가 가능

SELECT column_name FROM table1
UNION ALL 
SELECT column_name FROM table2;
  • 중복을 포함한 column_name 목록 반환 (UNION ALL)

✨ In English:

  • JOIN combines data from multiple tables based on a common column.

  • AS assigns an alias to a column or table.

  • INNER JOIN returns only matching records from both tables.

  • FULL OUTER JOIN returns all records, whether they match or not.

  • LEFT OUTER JOIN returns all records from the left table and matching records from the right table.

  • RIGHT JOIN returns all records from the right table and matching records from the left table.

  • UNION merges the results of two SELECT queries, removing duplicates.

  • UNION ALL merges results without removing duplicates.

📝 오늘 배운 점:

  • JOIN을 활용하면 여러 테이블의 데이터를 결합하여 보다 풍부한 정보를 얻을 수 있다.

  • AS는 컬럼이나 테이블 이름을 직관적으로 만들 때 유용하다.

  • INNER JOIN은 일치하는 데이터만 가져오므로 불필요한 데이터가 포함되지 않는다.

  • FULL OUTER JOIN은 모든 데이터를 가져와야 할 때 사용하지만 일부 DBMS에서는 지원하지 않는다.

  • LEFT JOIN과 RIGHT JOIN은 필요에 따라 NULL 값을 포함할 수 있어 데이터 분석 시 유용하다.

  • UNION을 사용하면 중복을 제거한 데이터를 가져올 수 있으며, UNION ALL을 사용하면 중복까지 포함된다.

  • 적절한 JOIN을 선택하는 것이 SQL 성능 최적화에 중요하다.

연습 문제

연습 문제 1

  • 캘리포니아 판매세법이 개정되어 고객들에게 이 내용을 알려야 한다. 캘리포니아에 살고 있는 고객의 이메일은 무엇인가?

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

    • 처음에는 아무 생각 없이 FULL JOIN을 사용했는데, FULL JOIN을 사용하면 customer.email이 없는 경우도 결과에 포함될 수 있기 때문에, INNER JOIN을 사용하는 게 더 적절한 선택 같다.

    • INNER JOIN 특징

      • INNER JOIN은 두 테이블에서 매칭되는 데이터만 반환

      • 즉, customer.address_id = address.address_id가 매칭되지 않으면 결과에서 제외됨(INNER JOIN을 사용하면 주소와 고객 정보가 반드시 연결된 데이터만 조회됨)

      • WHERE district = 'California' 조건을 추가하여 캘리포니아 지역만 필터링

연습 문제 2

  • Nick Wahlberg라는 배우의 광팬인 한 고객이 매장에 들어와서, 그가 출연한 영화를 찾고 싶다고 한다. Nick Wahlberg가 나오는 모든 영화 목록을 구하라.

      SELECT title, first_name, last_name  FROM film_actor
  INNER JOIN film ON film.film_id = film_actor.film_id
  INNER JOIN actor ON actor.actor_id = film_actor.actor_id -- 각 테이블을 연결할 때마다 JOIN ... ON을 명확히 적어줘야 함
  WHERE actor.first_name='Nick' AND actor.last_name = 'Wahlberg'

    • SQL에서 INNER JOIN을 사용할 때 여러 개의 테이블을 조인할 때는 JOIN ... ON을 각각 명확하게 지정해야 한다.

    • film_actor를 기준으로 조인 진행

      • film_actor는 중간 다리 역할을 하므로, 먼저 기준 테이블로 선택하는 것이 좋음

      • 벤다이어그램을 그려보면 조금 더 쉽게 접근할 수 있다

SQL