JOIN: 데이터를 연결하는 강력한 도구 ✨
- 여러 테이블에 흩어져 있는 데이터를 한 번에 조회하고 싶을 때, JOIN을 사용하면 효과적입니다.
- JOIN은 두 개 이상의 테이블을 공통된 값을 기준으로 결합하여, 하나의 통합된 결과를 만들어 줍니다.
- 조인을 사용하려면 테이블 간의 관계를 정의하는 조인 조건이 필요하며, 이는 주로 WHERE나 ON 절에 명시됩니다. 두 테이블 간의 공통된 컬럼을 기준으로 조건을 설정하죠.
- 일반적으로 PK (Primary Key)와 FK (Foreign Key) 관계를 통해 조인 조건을 만듭니다.
- PK는 각 테이블의 고유한 값을 나타내는 컬럼이고, FK는 다른 테이블의 PK를 참조하는 컬럼입니다.
JOIN 시 주의사항 ⚠️
- 어떤 테이블을 먼저 읽느냐에 따라 조인 작업의 성능이 달라질 수 있습니다.
- 특히 대량의 데이터를 처리할 때는 테이블 읽기 순서가 성능에 중요한 영향을 미칩니다.
1. INNER JOIN의 경우
- INNER JOIN에서는 테이블을 어떤 순서로 읽든 결과는 동일합니다.
- 다행히도 MySQL의 옵티마이저(optimizer)는 조인 순서를 자동으로 최적화합니다.
- 예를 들어, 더 작은 테이블을 먼저 읽은 후, 그 데이터를 기반으로 큰 테이블을 처리하는 방식 등 다양한 최적화 방법을 사용합니다.
2. OUTER JOIN의 경우
- OUTER JOIN은 INNER JOIN과 달리, 반드시 OUTER가 되는 테이블을 먼저 읽어야 합니다.
- LEFT OUTER JOIN은 왼쪽 테이블부터, RIGHT OUTER JOIN은 오른쪽 테이블부터 처리합니다.
- 이때 옵티마이저는 조인 순서를 선택할 수 없고, 정해진 순서대로 처리해야 합니다.
JOIN의 필요성 💡
상황 설명:
예를 들어, Products 테이블과 Suppliers 테이블이 있을 때, 제품 정보와 각 제품을 공급하는 공급업체 정보를 한 번에 조회하고 싶을 때 JOIN을 사용하면 됩니다.
예시 테이블 📊
Products 테이블:
| product_id | product_name | supplier_id | price |
|---|---|---|---|
| 1 | Laptop | 1 | 1000 |
| 2 | Phone | 2 | 500 |
| 3 | Tablet | 1 | 800 |
Suppliers 테이블:
| supplier_id | supplier_name | country |
|---|---|---|
| 1 | Supplier A | USA |
| 2 | Supplier B | UK |
SQL 쿼리 예시:
두 테이블의 공통 키인 supplier_id를 기준으로 제품과 공급업체 정보를 결합할 수 있습니다.
SELECT Products.product_name, Products.price, Suppliers.supplier_name, Suppliers.country
FROM Products
JOIN Suppliers ON Products.supplier_id = Suppliers.supplier_id;결과:
| product_name | price | supplier_name | country |
|---|---|---|---|
| Laptop | 1000 | Supplier A | USA |
| Phone | 500 | Supplier B | UK |
| Tablet | 800 | Supplier A | USA |
위의 쿼리는 서로 다른 테이블에서 공통된 값을 기준으로 데이터를 결합하여 하나의 결과로 조회하는 과정을 보여줍니다. JOIN을 통해 다양한 테이블의 데이터를 효율적으로 연결할 수 있습니다! 😊
INNER JOIN: 교집합을 찾아주는 기본적인 조인 🤝
- INNER JOIN은 가장 일반적인 조인의 형태로, 두 테이블의 교집합에 해당하는 데이터를 조회합니다.
- 흔히 동등 조인이라고 불리며, N개의 테이블을 조인할 때 N-1개의 조인 조건이 필요합니다.
- 조인 시, 동등 조건으로 사용되는 컬럼은 반드시 테이블 명을 명시해주어야 합니다.
INNER JOIN에서 ON을 이용한 JOIN 조건 지정 🎯
INNER JOIN에서 ON 절은 두 테이블을 공통된 값을 기준으로 결합할 때 자주 사용됩니다. 여기서 ON 절은 두 테이블 간의 관계를 명확하게 지정해주는 역할을 하죠.
예시 테이블 🎓
Employees 테이블:
| employee_id | employee_name | department_id |
|---|---|---|
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Charlie | 10 |
Departments 테이블:
| department_id | department_name |
|---|---|
| 10 | HR |
| 20 | Sales |
| 30 | IT |
INNER JOIN을 사용한 쿼리 예시:
SELECT Employees.employee_name, Departments.department_name
FROM Employees
INNER JOIN Departments ON Employees.department_id = Departments.department_id;결과 📊:
| employee_name | department_name |
|---|---|
| Alice | HR |
| Bob | Sales |
| Charlie | HR |
위의 쿼리는 Employees 테이블의 department_id와 Departments 테이블의 department_id가 같은 값을 기준으로, 각 직원의 부서를 조회하는 예시입니다.
INNER JOIN에서 USING을 이용한 JOIN 조건 지정 ✨
USING을 사용한 INNER JOIN은 두 테이블 간에 공통된 열 이름이 있을 때, 그 열을 기준으로 조인 조건을 지정하는 방법입니다. ON 절과 달리, USING은 두 테이블의 열 이름이 동일할 때만 사용할 수 있습니다.
예시 테이블 🎯
Employees 테이블:
| employee_id | employee_name | department_id |
|---|---|---|
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Charlie | 10 |
Departments 테이블:
| department_id | department_name |
|---|---|
| 10 | HR |
| 20 | Sales |
| 30 | IT |
USING을 사용한 INNER JOIN 쿼리 예시:
SELECT employee_name, department_name
FROM Employees
INNER JOIN Departments USING (department_id);결과 📊:
| employee_name | department_name |
|---|---|
| Alice | HR |
| Bob | Sales |
| Charlie | HR |
위의 쿼리는 USING 절을 통해 department_id 컬럼을 기준으로, 두 테이블의 데이터를 결합하는 방법을 보여줍니다. 공통된 컬럼 이름이 있을 때, 더 간결한 방식으로 조인 조건을 지정할 수 있는 장점이 있죠! 😎
💡 JOIN에서 ON과 WHERE의 차이
JOIN을 사용할 때 ON과 WHERE는 각기 다른 역할을 하며, 데이터 필터링 방식에 차이가 있습니다. 두 절의 차이를 명확히 이해하면 더 정확하고 효율적인 쿼리를 작성할 수 있습니다.
여기서 잠깐! ON과 WHERE 차이 🔍
1. ON 절 (JOIN 조건)
ON은 JOIN할 때 두 테이블을 어떻게 연결할지 정의하는 절입니다.
즉, 테이블을 결합할 기준이 되는 조건을 설정하는 역할을 합니다.
예시
SELECT *
FROM employees e
JOIN departments d ON e.department_id = d.department_id;이 쿼리는 employees 테이블과 departments 테이블을 department_id로 연결합니다.
2. WHERE 절 (필터링 조건)
WHERE는 JOIN 이후의 결과에서 특정 조건을 만족하는 데이터를 필터링하는 데 사용됩니다.
즉, JOIN 후의 데이터를 추가로 걸러내는 역할을 합니다.
예시
SELECT *
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000;위 쿼리는 employees와 departments를 JOIN한 후, salary가 5,000 이상인 직원만 필터링합니다.
ON과 WHERE 차이 💡
- ON 절은 두 테이블 간의 JOIN 조건을 정의합니다.
- WHERE 절은 JOIN 후 필터링을 수행하여 최종 결과를 조정합니다.
여기서 잠깐! LEFT JOIN에서 ON과 WHERE 차이 🤔
LEFT JOIN에서는 ON과 WHERE의 차이가 더 뚜렷해집니다:
- ON 절은 NULL을 포함한 모든 결과를 유지합니다.
- WHERE 절은 NULL 값을 포함한 결과를 필터링할 수 있습니다.
예시 비교
ON 절에서 필터링:
SELECT *
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id AND d.location = 'Seoul';LEFT JOIN으로 모든 직원을 포함하며, 부서의 위치가Seoul인 경우만 매칭됩니다.
WHERE 절에서 필터링:
SELECT *
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.location = 'Seoul';- 이 경우
LEFT JOIN결과 중Seoul에 해당하는 데이터만 남고, 나머지는 제외됩니다.
NATURAL JOIN: 자동으로 일어나는 조인 🤝✨
NATURAL JOIN은 두 테이블에서 동일한 이름을 가진 모든 열을 자동으로 조인 조건으로 삼아 조인을 수행하는 방식입니다. ON이나 USING 절을 명시하지 않아도 동일한 열 이름이 있을 경우 자동으로 해당 열을 기준으로 데이터를 결합해줍니다.
NATURAL JOIN의 특징 🔍
- 동일한 이름을 가진 열이 있으면, 자동으로 조인 조건이 설정됩니다.
- 결과에서는 동일한 열은 한 번만 출력됩니다.
- 동일한 이름을 가진 열이 없다면 카티션 곱(Cartesian product)이 발생할 수 있으니 주의해야 합니다.
NATURAL JOIN 예시 📝
Employees 테이블:
| employee_id | employee_name | department_id |
|---|---|---|
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Charlie | 10 |
Departments 테이블:
| department_id | department_name |
|---|---|
| 10 | HR |
| 20 | Sales |
| 30 | IT |
NATURAL JOIN을 사용한 쿼리:
SELECT employee_name, department_name
FROM Employees
NATURAL JOIN Departments;결과 📊:
| employee_name | department_name |
|---|---|
| Alice | HR |
| Bob | Sales |
| Charlie | HR |
department_id라는 동일한 열을 자동으로 기준으로 잡아, 두 테이블의 데이터를 결합했습니다.
NATURAL JOIN의 문제점: 동일한 열이 여러 개일 때 ⚠️
문제 상황:
두 테이블 Students와 Classes가 class_id를 기준으로 조인해야 하는데, 두 테이블 모두 year라는 공통 열도 가지고 있어 NATURAL JOIN을 사용할 경우 의도치 않게 year 열도 포함되어 조인됩니다.
Students 테이블:
| student_id | student_name | class_id | year |
|---|---|---|---|
| 1 | Alice | 101 | 2023 |
| 2 | Bob | 102 | 2022 |
| 3 | Charlie | 101 | 2022 |
Classes 테이블:
| class_id | class_name | year |
|---|---|---|
| 101 | Math | 2023 |
| 102 | Science | 2023 |
| 103 | History | 2022 |
NATURAL JOIN을 사용한 쿼리:
SELECT s.student_id, s.student_name, c.class_name
FROM Students s
NATURAL JOIN Classes c;결과 📊:
| student_id | student_name | class_name |
|---|---|---|
| 1 | Alice | Math |
문제점 설명 🚨
- NATURAL JOIN은
class_id뿐만 아니라 공통된 열인year도 자동으로 조인 조건에 포함되기 때문에, 두 열 모두 일치해야 조인이 성립됩니다. - 그 결과, Alice만이
class_id = 101과year = 2023으로 일치하여 조회되었습니다. - Charlie는
class_id = 101이 맞지만year = 2022이기 때문에 결과에서 제외되었습니다. - Bob은
class_id = 102이 맞지만year = 2022라서Classes테이블의year = 2023과 일치하지 않아 결과에 포함되지 않았습니다.
해결 방법 💡
이 문제를 해결하려면 NATURAL JOIN 대신 ON 절을 사용하여 원하는 열(class_id)만을 기준으로 명확하게 조인 조건을 지정해 주어야 합니다.
SELECT s.student_id, s.student_name, c.class_name
FROM Students s
JOIN Classes c ON s.class_id = c.class_id;이렇게 ON 절을 사용하여 class_id만을 기준으로 명확하게 지정하면, year 열이 조인 조건에 포함되지 않아, 의도한 대로 데이터를 조회할 수 있습니다. 😊
OUTER JOIN: 더 넓은 범위의 데이터 조회 🌍
- INNER JOIN은 두 테이블 간에 일치하는 데이터만을 결과로 반환하는 반면, OUTER JOIN은 어느 한쪽에만 있는 데이터도 반환할 수 있습니다.
- OUTER JOIN을 사용하면 한쪽 테이블에는 데이터가 없지만, 다른 쪽 테이블에만 있는 데이터도 결과에 포함됩니다. 일치하지 않는 데이터는 NULL 값으로 채워지죠.
- LEFT JOIN(LEFT OUTER JOIN)과 RIGHT JOIN(RIGHT OUTER JOIN), 그리고 양쪽의 모든 데이터를 포함하는 FULL JOIN이 있습니다.
LEFT OUTER JOIN: 왼쪽 테이블이 중심! 📝
LEFT OUTER JOIN은 왼쪽 테이블의 모든 행을 반환하고, 오른쪽 테이블에서 일치하는 데이터가 있을 경우 함께 반환합니다. 오른쪽 테이블에 일치하지 않는 데이터는 NULL로 표시됩니다.
예시 테이블 📊
Customers 테이블:
| customer_id | customer_name |
|---|---|
| 1 | John Doe |
| 2 | Jane Smith |
| 3 | Max Payne |
Orders 테이블:
| order_id | customer_id | order_amount |
|---|---|---|
| 101 | 1 | 100 |
| 102 | 2 | 150 |
LEFT OUTER JOIN 쿼리 예시:
SELECT c.customer_name, o.order_amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;결과 📊:
| customer_name | order_amount |
|---|---|
| John Doe | 100 |
| Jane Smith | 150 |
| Max Payne | NULL |
Max Payne의 경우 주문이 없지만, LEFT JOIN으로 인해 Customers 테이블의 모든 데이터가 반환되므로, 주문 정보가 없는 경우에는 NULL로 표시됩니다.
RIGHT OUTER JOIN: 오른쪽 테이블이 중심! 🔄
RIGHT OUTER JOIN은 오른쪽 테이블의 모든 행을 반환하고, 왼쪽 테이블에서 일치하는 데이터가 있을 경우 함께 반환됩니다. 왼쪽 테이블에 일치하지 않는 데이터는 NULL로 표시됩니다.
예시 테이블 📊
Customers 테이블:
| customer_id | customer_name |
|---|---|
| 1 | John Doe |
| 2 | Jane Smith |
| 3 | Max Payne |
Orders 테이블:
| order_id | customer_id | order_amount |
|---|---|---|
| 101 | 1 | 100 |
| 102 | 2 | 150 |
| 103 | 4 | 200 |
RIGHT OUTER JOIN 쿼리 예시:
SELECT c.customer_name, o.order_amount
FROM Customers c
RIGHT JOIN Orders o ON c.customer_id = o.customer_id;결과 📊:
| customer_name | order_amount |
|---|---|
| John Doe | 100 |
| Jane Smith | 150 |
| NULL | 200 |
RIGHT JOIN을 사용하여 오른쪽 테이블의 모든 데이터가 반환되고, 일치하지 않는 Customers의 데이터는 NULL로 표시되었습니다. order_id = 103의 경우, customer_id = 4는 Customers 테이블에 존재하지 않기 때문에 NULL로 출력되었습니다.
FULL OUTER JOIN: 모든 데이터를 다 가져와! 🌐
FULL OUTER JOIN은 LEFT JOIN과 RIGHT JOIN의 결과를 합친 것과 같습니다. 양쪽 테이블의 모든 데이터를 포함하고, 일치하지 않는 데이터는 NULL로 반환됩니다.
예시 테이블 📊
Customers 테이블:
| customer_id | customer_name |
|---|---|
| 1 | John Doe |
| 2 | Jane Smith |
| 3 | Max Payne |
Orders 테이블:
| order_id | customer_id | order_amount |
|---|---|---|
| 101 | 1 | 100 |
| 102 | 2 | 150 |
| 103 | 4 | 200 |
FULL OUTER JOIN 쿼리 예시 (다른 DBMS에서는 가능하지만, MySQL에서는 직접 지원하지 않음):
SELECT c.customer_name, o.order_amount
FROM Customers c
FULL OUTER JOIN Orders o ON c.customer_id = o.customer_id;결과 📊:
| customer_name | order_amount |
|---|---|
| John Doe | 100 |
| Jane Smith | 150 |
| Max Payne | NULL |
| NULL | 200 |
Max Payne과 order_id = 103 모두 결과에 포함되며, 서로 일치하지 않는 부분은 NULL로 출력됩니다.
MySQL에서 FULL OUTER JOIN을 구현하는 방법 🛠️
MySQL은 FULL OUTER JOIN을 직접적으로 지원하지 않지만, LEFT JOIN과 RIGHT JOIN을 UNION으로 조합하여 비슷한 결과를 얻을 수 있습니다.
SELECT c.customer_name, o.order_amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
UNION
SELECT c.customer_name, o.order_amount
FROM Customers c
RIGHT JOIN Orders o ON c.customer_id = o.customer_id;이 방법으로 MySQL에서도 FULL OUTER JOIN과 유사한 결과를 만들어낼 수 있습니다! 😊
SELF JOIN: 같은 테이블에서 관계를 찾는 조인 🔄
SELF JOIN은 같은 테이블을 스스로 조인하는 방식으로, 하나의 테이블을 두 개의 다른 집합처럼 취급하여 테이블 내에서 특정한 관계를 찾을 때 사용됩니다. 이는 주로 계층적 구조나 상호 참조 관계를 처리할 때 유용합니다.
SELF JOIN의 특징 🌟
- 동일한 테이블을 두 번 사용하므로, 테이블에 대해 두 개의 별칭을 지정해야 합니다.
- 테이블 내에서 서로 관련된 행을 매칭하여 조인할 때 사용됩니다. 예를 들어, 같은 테이블에서 직원과 상사와 같은 관계를 표현할 때 유용합니다.
예시 상황: 직원과 상사 관계 🌱
회사의 Employees 테이블에서 각 직원의 상사(관리자)를 찾고 싶다고 가정해봅시다. 직원과 상사 모두 같은 Employees 테이블에 있으며, 각 직원의 상사 정보는 manager_id로 기록되어 있습니다.
예시 테이블 📊
Employees 테이블:
| employee_id | employee_name | manager_id |
|---|---|---|
| 1 | John Doe | 3 |
| 2 | Jane Smith | 3 |
| 3 | Max Payne | NULL |
| 4 | Emily Clark | 1 |
여기서 John Doe와 Jane Smith의 상사는 Max Payne이고, Emily Clark의 상사는 John Doe인 상황입니다. Max Payne은 상사가 없기 때문에 manager_id가 NULL입니다.
SELF JOIN 쿼리 예시 💻
SELECT e.employee_name AS employee, m.employee_name AS manager
FROM Employees e
LEFT JOIN Employees m ON e.manager_id = m.employee_id;이 쿼리에서 Employees 테이블을 두 번 사용하고, 각각에 대해 별칭을 e와 m으로 지정하여 서로 다른 역할(직원과 상사)을 부여합니다. LEFT JOIN을 사용하여 상사가 없는 직원도 조회할 수 있습니다.
결과 📊
| employee | manager |
|---|---|
| John Doe | Max Payne |
| Jane Smith | Max Payne |
| Max Payne | NULL |
| Emily Clark | John Doe |
이 결과에서 각 직원이 누구의 관리하에 있는지 알 수 있습니다. 예를 들어, John Doe와 Jane Smith는 Max Payne을 상사로 두고 있으며, Emily Clark은 John Doe를 상사로 두고 있죠. Max Payne은 상사가 없기 때문에 NULL로 표시됩니다.
SELF JOIN의 용도 🛠️
- 계층적 구조를 나타낼 때, 예를 들어 상사-직원 관계, 부모-자식 관계 등을 표현하는 데 매우 유용합니다.
- 같은 테이블 내에서 상호 참조 관계를 찾거나 데이터 간의 연결을 표현할 때도 활용됩니다.
- SELF JOIN을 통해 복잡한 구조 속에서 명확한 관계를 도출할 수 있습니다.
Non-Equi JOIN: 등호(=)를 넘어서 🌟
Non-Equi Join은 테이블 간의 데이터를 조인할 때 = (등호) 대신에 비교 연산자(>, <, >=, <=, != 등)를 사용하는 조인 방식입니다. 일반적인 조인에서는 Primary Key (PK)와 Foreign Key (FK) 관계에 기반한 등호 조인을 사용하지만, Non-Equi Join은 이를 넘어서 범위나 조건에 맞는 데이터를 조인할 수 있게 해줍니다.
Non-Equi Join의 특징 🔍
- 등호 외의 비교 연산자를 사용하여 테이블을 조인합니다.
- 주로 PK-FK 관계가 아닌 컬럼 간의 비교가 필요할 때 사용됩니다.
- 조건을 통해 특정 범위나 조건에 맞는 데이터를 조인할 수 있어 더 유연한 데이터 매칭이 가능합니다.
예시 상황: 주문 금액에 따른 할인 등급 적용 🛒
고객의 주문 금액에 따라 할인 등급을 적용하는 시스템이 있다고 가정해 봅시다. Non-Equi Join을 사용하여 주문 금액 범위에 맞는 할인 등급을 적용할 수 있습니다.
예시 테이블 📊
Orders 테이블 (주문 정보):
| order_id | customer_id | order_amount |
|---|---|---|
| 1 | 101 | 50 |
| 2 | 102 | 150 |
| 3 | 103 | 300 |
| 4 | 104 | 450 |
Discounts 테이블 (할인 등급):
| discount_id | min_amount | max_amount | discount_rate |
|---|---|---|---|
| 1 | 0 | 100 | 5% |
| 2 | 101 | 200 | 10% |
| 3 | 201 | 400 | 15% |
| 4 | 401 | 1000 | 20% |
Non-Equi Join 쿼리 예시 💻
이제 주문 금액에 맞는 할인 등급을 범위로 매칭해보겠습니다. Orders 테이블과 Discounts 테이블을 Non-Equi Join을 사용하여 min_amount와 max_amount 사이에 해당하는 주문 금액을 찾아서 조인합니다.
SELECT o.order_id, o.order_amount, d.discount_rate
FROM Orders o
JOIN Discounts d
ON o.order_amount BETWEEN d.min_amount AND d.max_amount;결과 📊:
| order_id | order_amount | discount_rate |
|---|---|---|
| 1 | 50 | 5% |
| 2 | 150 | 10% |
| 3 | 300 | 15% |
| 4 | 450 | 20% |
- order_id 1은 금액이 50이므로 5% 할인을 받습니다.
- order_id 2는 금액이 150이므로 10% 할인을 받고,
- order_id 3은 300, 15% 할인을 받습니다.
- 마지막으로 order_id 4는 450으로 20% 할인을 받게 됩니다.
Non-Equi Join이 유용한 경우 💡
- 금액에 따라 등급이나 할인을 결정할 때.
- 날짜 범위에 따라 데이터를 매칭할 때, 예를 들어 이벤트 기간 동안의 판매 내역을 조회하는 경우.
- 특정한 값의 범위나 조건을 기준으로 데이터를 매칭할 때.
- 범위 검색이 필요한 상황에서 매우 유용하게 사용할 수 있습니다.
이렇게 Non-Equi Join을 활용하면 단순한 일치뿐만 아니라 범위나 조건에 따라 데이터를 유연하게 연결할 수 있습니다. 😊
Subquery: 쿼리 안의 쿼리 🧐
서브 쿼리(Subquery)는 하나의 쿼리 내에 또 다른 SELECT 문을 작성하는 방식으로, 복잡한 조건을 처리하거나 특정한 데이터를 필터링할 때 사용됩니다. 서브 쿼리는 비교 연산자와 함께 사용되어 외부 쿼리의 조건을 만족시키는 데이터를 제공하는 역할을 하며, 보통 괄호 ()로 감싸서 작성됩니다.
Subquery의 특징 🌟
- 외부 쿼리(메인 쿼리) 안에 존재하는 내부 쿼리입니다.
- 비교 연산자(
=,>,<,IN등)를 사용해 내부 쿼리의 결과를 외부 쿼리와 비교합니다. - 서브 쿼리는 데이터 필터링이나 특정 조건을 만족하는 데이터를 추출하는 데 매우 유용합니다.
예시 상황: 주문된 제품만 조회하기 💡
우리는 제품 테이블에서 주문이 이루어진 적이 있는 제품들만 조회하려고 합니다. 이때 서브 쿼리를 사용하여 주문된 제품을 필터링할 수 있습니다. 즉, Orders 테이블에 있는 product_id 값과 Products 테이블의 product_id를 비교하여, 주문된 제품만 가져오는 방식입니다.
예시 테이블 📊
Products 테이블:
| product_id | product_name | price |
|---|---|---|
| 1 | Laptop | 1000 |
| 2 | Phone | 500 |
| 3 | Tablet | 800 |
| 4 | Monitor | 300 |
Orders 테이블:
| order_id | product_id | quantity |
|---|---|---|
| 101 | 1 | 2 |
| 102 | 2 | 1 |
| 103 | 1 | 1 |
Subquery 쿼리 예시 💻
이제 서브 쿼리를 사용하여 Orders 테이블에 존재하는 product_id 값을 기준으로, Products 테이블에서 해당 제품만 필터링해봅시다.
SELECT product_name, price
FROM Products
WHERE product_id IN (SELECT product_id FROM Orders);결과 📊:
| product_name | price |
|---|---|
| Laptop | 1000 |
| Phone | 500 |
- Laptop과 Phone은 Orders 테이블에 존재하는
product_id값에 해당하므로 조회됩니다. - Tablet과 Monitor는 주문된 적이 없기 때문에 결과에서 제외되었습니다.
Subquery의 사용 시나리오 💡
- 특정 데이터의 존재 여부를 확인하고, 그 결과를 바탕으로 데이터를 필터링할 때.
- 복잡한 조건을 만족하는 데이터를 비교 연산자(
=,IN,EXISTS등)를 통해 추출할 때. - 서브 쿼리는 종종 다른 테이블에서 값이 존재하는지 확인하거나, 최대값/최소값과 같은 통계 데이터를 이용해 필터링할 때 사용됩니다.
서브 쿼리는 이렇게 복잡한 조건을 처리할 수 있는 강력한 도구입니다. 예를 들어, 특정 조건을 만족하는 데이터를 다른 테이블의 결과와 비교하여 간단하게 조회할 수 있죠! 😊
Nested Subquery(중첩 서브 쿼리): 쿼리 속 쿼리의 깊은 활용 💡
Nested Subquery는 서브 쿼리가 여러 단계로 중첩된 형태를 말합니다. 즉, 하나의 쿼리 내에 또 다른 쿼리가 있고, 그 안에 또 다른 쿼리가 있을 수 있는 구조를 갖습니다. 이를 통해 복잡한 데이터를 효율적으로 필터링하거나, 특정 조건을 만족하는 데이터를 추출할 수 있습니다.
단일 행 서브쿼리: 단일 값을 반환하는 서브쿼리 🌟
단일 행 서브쿼리는 서브쿼리의 결과가 단일 행만 반환될 때 사용됩니다. 주로 비교 연산자(=, >, < 등)를 사용하여 외부 쿼리와 값을 비교합니다.
예시 상황: 가장 많은 금액을 지불한 고객의 이름 조회 📊
고객과 주문 테이블에서 가장 많은 금액을 지불한 고객의 이름을 조회하는 상황입니다.
예시 테이블:
Customers 테이블:
| customer_id | customer_name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
Orders 테이블:
| order_id | customer_id | total_amount |
|---|---|---|
| 101 | 1 | 300 |
| 102 | 2 | 500 |
| 103 | 3 | 400 |
단일 행 서브쿼리 예시 💻
SELECT customer_name
FROM Customers
WHERE customer_id = (SELECT customer_id
FROM Orders
ORDER BY total_amount DESC
LIMIT 1);결과 📊:
| customer_name |
|---|
| Bob |
위 쿼리는 가장 큰 주문 금액을 지불한 고객을 찾고, 그 고객의 이름을 출력합니다. Bob이 가장 많은 금액인 500을 지불했으므로, 결과에 표시됩니다.
다중 행 서브쿼리: 여러 값을 반환하는 서브쿼리 🌐
다중 행 서브쿼리는 서브쿼리의 결과가 여러 행을 반환할 때 사용됩니다. 이 경우 IN, ANY, ALL 등의 연산자를 사용하여 외부 쿼리와 비교할 수 있습니다.
1. IN 연산자를 사용한 다중 행 서브쿼리
IN 연산자는 서브쿼리에서 반환된 여러 값 중 하나라도 일치하는지를 확인합니다.
예시 상황: 주문이 있는 고객 조회 🛒
SELECT customer_name
FROM Customers
WHERE customer_id IN (SELECT customer_id FROM Orders);결과 📊:
| customer_name |
|---|
| Alice |
| Bob |
| Charlie |
이 쿼리는 주문을 한 고객들을 조회하는 예시입니다. Orders 테이블에 존재하는 customer_id 값을 IN 연산자로 조회하여 모든 고객이 반환되었습니다.
2. ANY 연산자를 사용한 다중 행 서브쿼리
ANY 연산자는 서브쿼리에서 반환된 값 중 하나라도 외부 쿼리의 조건을 만족하면 참을 반환합니다.
예시 상황: 주문 금액이 500보다 적은 고객 조회
SELECT customer_name
FROM Customers
WHERE customer_id = ANY (SELECT customer_id FROM Orders WHERE total_amount < 500);결과 📊:
| customer_name |
|---|
| Alice |
| Charlie |
이 쿼리는 주문 금액이 500보다 작은 고객들을 조회합니다. Alice와 Charlie가 조건에 해당하여 결과로 반환됩니다.
3. ALL 연산자를 사용한 다중 행 서브쿼리
ALL 연산자는 서브쿼리에서 반환된 값 모두를 외부 쿼리가 만족해야 참을 반환합니다.
예시 상황: 모든 주문이 300보다 큰 금액을 지불한 고객 조회
SELECT customer_name
FROM Customers
WHERE customer_id = ALL (SELECT customer_id FROM Orders WHERE total_amount > 300);결과 📊:
| customer_name |
|---|
| Bob |
| Charlie |
이 쿼리는 모든 주문 금액이 300보다 큰 고객들을 조회합니다. Bob과 Charlie는 조건을 모두 만족하므로 결과로 반환됩니다.
다중 열 서브쿼리: 두 개 이상의 열을 반환하는 서브쿼리 🌐
다중 열 서브쿼리는 서브쿼리에서 두 개 이상의 열을 반환하며, 주로 튜플 형식으로 비교합니다. IN 또는 EXISTS 연산자를 사용해 여러 열을 한꺼번에 비교할 수 있습니다.
예시 상황: 특정 제품과 주문 정보를 가진 주문 내역 조회
예시 테이블:
Orders 테이블:
| order_id | product_id | customer_id | total_amount |
|---|---|---|---|
| 101 | 1 | 201 | 300 |
| 102 | 2 | 202 | 500 |
| 103 | 3 | 203 | 400 |
| 104 | 1 | 204 | 600 |
Returned_Orders 테이블:
| return_order_id | order_id | product_id | return_reason |
|---|---|---|---|
| 201 | 101 | 1 | Defective |
| 202 | 102 | 2 | Wrong item |
다중 열 서브쿼리 예시 💻
SELECT order_id, product_id, total_amount
FROM Orders
WHERE (order_id, product_id) IN (SELECT order_id, product_id FROM Returned_Orders);결과 📊:
| order_id | product_id | total_amount |
|---|---|---|
| 101 | 1 | 300 |
| 102 | 2 | 500 |
이 쿼리는 반품된 주문의 주문 ID와 제품 ID가 Orders 테이블과 일치하는 경우를 반환합니다.
Nested Subquery의 장점 🌟
- 복잡한 데이터를 간단하게 필터링하거나 추출할 수 있습니다.
- 비교 연산자, 다중 행, 다중 열 서브쿼리를 통해 다양한 방식으로 데이터를 비교하고 조회할 수 있습니다.
이렇게 Nested Subquery를 사용하면 복잡한 데이터를 효율적으로 처리할 수 있습니다! 😊
Inline View: 동적 임시 테이블 활용하기 📊
Inline View는 쿼리의 FROM 절에 서브 쿼리를 사용하여 마치 임시 테이블처럼 데이터를 처리하는 방식입니다. 이 서브 쿼리는 쿼리 실행이 완료되면 사라지며, 물리적으로 데이터베이스에 저장되지 않지만, 외부 쿼리에서 다른 테이블처럼 참조할 수 있습니다. Inline View는 복잡한 쿼리를 간결하게 처리하고, 페이지네이션이나 순위 부여 등의 다양한 용도로 활용됩니다.
Inline View의 특징 🌟
- FROM 절에 서브 쿼리를 작성하여 임시 테이블처럼 사용할 수 있습니다.
- 쿼리 내에서 뷰(View)처럼 동작하며, 결과를 동적으로 생성하여 한 번 사용된 후 쿼리 실행이 끝나면 사라집니다.
- 서브 쿼리에서 생성된 컬럼을 외부 쿼리에서 자유롭게 참조할 수 있어 복잡한 데이터를 간결하게 처리할 수 있습니다.
Inline View 예시 💡
다음은 Products 테이블에서 평균 가격보다 비싼 제품들을 조회하는 Inline View 예시입니다. 서브 쿼리를 FROM 절에 사용하여 임시 테이블을 생성한 후, 그 결과를 이용하여 외부 쿼리를 실행합니다.
예시 테이블:
Products 테이블:
| product_id | product_name | price |
|---|---|---|
| 1 | Laptop | 1000 |
| 2 | Phone | 500 |
| 3 | Tablet | 800 |
Inline View 쿼리 예시 💻
SELECT product_name, avg_price
FROM (
SELECT product_name, price, AVG(price) OVER () AS avg_price
FROM Products
) AS Temp
WHERE price > avg_price;결과 📊:
| product_name | avg_price |
|---|---|
| Laptop | 766.67 |
위 쿼리는 Products 테이블에서 제품의 평균 가격을 계산한 후, 그보다 높은 가격을 가진 제품만을 조회하는 예시입니다. Laptop의 가격이 평균 가격보다 높기 때문에 결과에 포함되었습니다.
Inline View와 TOP-N 질의: 상위 N개 데이터 조회하기 🥇
TOP-N 질의는 특정 조건에 따라 상위 N개의 행을 조회하는 방식입니다. Inline View를 사용하면 동적으로 임시 테이블을 생성하고, 그 안에서 상위 N개의 데이터를 추출할 수 있습니다.
다음 예시는 Products 테이블에서 가장 비싼 상위 5개 제품을 조회하는 방식입니다.
예시 테이블:
Products 테이블:
| product_id | product_name | price |
|---|---|---|
| 1 | Laptop | 1000 |
| 2 | Phone | 500 |
| 3 | Tablet | 800 |
| 4 | Monitor | 300 |
| 5 | Headphones | 200 |
| 6 | Keyboard | 150 |
| 7 | Mouse | 100 |
| 8 | Printer | 400 |
| 9 | Webcam | 250 |
| 10 | Desk | 350 |
Inline View와 TOP-N 쿼리 예시 💻
SELECT b.rn, b.product_id, b.product_name, b.price
FROM (
SELECT @rownum := @rownum + 1 AS rn, a.*
FROM (
SELECT product_id, product_name, price
FROM Products
ORDER BY price DESC
) a, (SELECT @rownum := 0) tmp
) b
WHERE b.rn <= 5;결과 📊:
| rn | product_id | product_name | price |
|---|---|---|---|
| 1 | 1 | Laptop | 1000 |
| 2 | 3 | Tablet | 800 |
| 3 | 2 | Phone | 500 |
| 4 | 8 | Printer | 400 |
| 5 | 10 | Desk | 350 |
위 쿼리는 가격 기준으로 상위 5개의 제품을 조회합니다. @rownum 변수를 사용하여 각 행에 순번을 매기고, 그 순번을 기준으로 상위 5개의 제품을 가져왔습니다.
Inline View와 페이지네이션: 특정 페이지의 데이터 조회 📄
다음은 페이지네이션을 위해 Inline View와 변수를 사용하여, 2페이지에 해당하는 제품 목록을 동적으로 조회하는 예시입니다. 각 페이지에 5개의 제품만 표시하는 방식입니다.
페이지네이션 쿼리 예시 💻
SET @pageno = 2; -- 페이지 번호 설정
SELECT b.rn, b.product_id, b.product_name, b.price
FROM (
SELECT @rownum := @rownum + 1 AS rn, a.*
FROM (
SELECT product_id, product_name, price
FROM Products
ORDER BY price DESC
) a, (SELECT @rownum := 0) tmp
) b
WHERE b.rn > (@pageno * 5 - 5) AND b.rn <= (@pageno * 5);결과 📊:
| rn | product_id | product_name | price |
|---|---|---|---|
| 6 | 6 | Keyboard | 150 |
| 7 | 9 | Webcam | 250 |
| 8 | 10 | Desk | 350 |
| 9 | 8 | Printer | 400 |
| 10 | 4 | Monitor | 300 |
이 쿼리는 2페이지에 해당하는 6번째부터 10번째까지의 제품을 조회합니다. 각 페이지에는 5개의 제품만 표시되며, 페이지 번호는 @pageno 변수를 사용해 동적으로 변경할 수 있습니다.
Inline View와 LIMIT을 사용한 페이지네이션 예시:
LIMIT을 사용하여 특정 범위의 데이터를 페이지 단위로 가져올 수도 있습니다. 다음은 6번째부터 10번째 제품을 조회하는 쿼리입니다.
SELECT a.*
FROM (
SELECT @rownum := @rownum + 1 AS rn, product_id, product_name, price
FROM Products p, (SELECT @rownum := 0) tmp
ORDER BY price DESC
) a
LIMIT 5, 5;결과 📊:
| rn | product_id | product_name | price |
|---|---|---|---|
| 6 | 6 | Keyboard | 150 |
| 7 | 9 | Webcam | 250 |
| 8 | 10 | Desk | 350 |
| 9 | 8 | Printer | 400 |
| 10 | 4 | Monitor | 300 |
이 쿼리는 Inline View와 LIMIT을 결합하여 특정 범위의 데이터를 간결하게 처리하는 예시입니다. 6번째부터 10번째 제품을 반환하는 방식입니다.
Inline View의 장점:
- 복잡한 쿼리를 간결하게 처리할 수 있습니다.
- 임시 테이블처럼 데이터를 동적으로 생성하여 뷰처럼 활용할 수 있습니다.
- TOP-N 질의, 페이지네이션, 순위 부여 등의 작업에서 효율적입니다.
Inline View는 다양한 상황에서 유용하게 사용할 수 있는 강력한 도구입니다! 😊
Scalar Subquery: 단일 값 반환 서브쿼리 🎯
Scalar Subquery는 단일 값을 반환하는 서브 쿼리로, 한 개의 행과 한 개의 열만을 결과로 돌려줍니다. 이 서브 쿼리는 일반적인 값처럼 취급되며, SELECT 문에서 하나의 컬럼처럼 사용할 수 있습니다. 스칼라 서브쿼리는 조건을 정의하거나 특정 값의 계산에 유용하게 사용됩니다.
Scalar Subquery의 특징 🌟
- 항상 단일 값을 반환해야 하며, 결과는 하나의 값으로만 표현됩니다.
- SELECT 문 내에서 컬럼처럼 사용될 수 있으며, 특정 연산을 수행하여 값을 반환합니다.
- 하위 쿼리의 결과가 단일 행과 단일 열을 만족하지 않으면 오류가 발생합니다.
예시 상황: 제품 가격과 평균 가격 조회 💡
우리는 Products 테이블에서 각 제품의 가격과 전체 평균 가격을 함께 조회하려고 합니다. 여기서 스칼라 서브쿼리를 사용하여 전체 평균 가격을 계산하고, 각 제품의 가격과 함께 출력합니다.
예시 테이블 📊
Products 테이블:
| product_id | product_name | price |
|---|---|---|
| 1 | Laptop | 1000 |
| 2 | Phone | 500 |
| 3 | Tablet | 800 |
| 4 | Monitor | 300 |
| 5 | Headphones | 200 |
Scalar Subquery 쿼리 예시 💻
SELECT product_name, price,
(SELECT AVG(price) FROM Products) AS avg_price
FROM Products;결과 📊:
| product_name | price | avg_price |
|---|---|---|
| Laptop | 1000 | 560.00 |
| Phone | 500 | 560.00 |
| Tablet | 800 | 560.00 |
| Monitor | 300 | 560.00 |
| Headphones | 200 | 560.00 |
위 쿼리는 Products 테이블의 각 제품 가격을 출력하면서, 스칼라 서브쿼리를 통해 전체 평균 가격을 계산하여 각 행에 동일한 값으로 표시합니다. 평균 가격 560은 모든 제품에 대해 동일하게 나타납니다.
Scalar Subquery의 사용 예시 💡
스칼라 서브쿼리는 다양한 상황에서 사용할 수 있습니다. 다음은 스칼라 서브쿼리를 활용할 수 있는 예시들입니다.
- 특정 조건에 맞는 값 계산하기:
- 예를 들어, 특정 제품의 가격이 전체 평균 가격보다 높은지 여부를 확인할 수 있습니다.
SELECT product_name, price,
CASE WHEN price > (SELECT AVG(price) FROM Products)
THEN 'Above Average'
ELSE 'Below Average'
END AS price_comparison
FROM Products;결과 📊:
| product_name | price | price_comparison |
|---|---|---|
| Laptop | 1000 | Above Average |
| Phone | 500 | Below Average |
| Tablet | 800 | Above Average |
| Monitor | 300 | Below Average |
| Headphones | 200 | Below Average |
위 쿼리는 스칼라 서브쿼리를 사용하여 각 제품의 가격이 전체 평균 가격보다 높은지 여부를 판별합니다. Laptop과 Tablet은 평균 가격보다 높아 "Above Average"로 표시되며, 나머지 제품은 "Below Average"로 표시됩니다.
- 다른 테이블의 단일 값 참조하기:
- 다른 테이블에서 값을 계산하여, 그 값을 스칼라 서브쿼리로 참조할 수 있습니다.
SELECT product_name, price,
(SELECT COUNT(*) FROM Orders WHERE Orders.product_id = Products.product_id) AS order_count
FROM Products;이 쿼리는 Products 테이블에서 각 제품의 주문 수를 Orders 테이블에서 조회하여 출력합니다.
Scalar Subquery의 장점:
- 단일 값을 반환하기 때문에 SELECT 문 내에서 계산된 값을 간편하게 참조할 수 있습니다.
- 복잡한 계산이나 값을 비교할 때, 다른 테이블의 데이터를 쉽게 컬럼처럼 활용할 수 있습니다.
이처럼 Scalar Subquery는 다양한 상황에서 효율적인 데이터 처리를 가능하게 해주는 강력한 도구입니다! 😊
Subquery 활용: 데이터 조작과 서브 쿼리의 응용 🎯
서브 쿼리는 데이터를 조회하는 데만 사용되는 것이 아니라, CREATE, INSERT, UPDATE, DELETE와 같은 데이터 조작에서도 매우 유용하게 활용됩니다. 서브 쿼리를 사용하면 복잡한 조건을 통해 데이터를 쉽게 필터링하고, 그 결과를 테이블 생성이나 수정에 적용할 수 있습니다.
1. CREATE TABLE (테이블 생성)
서브 쿼리를 사용하여 새로운 테이블을 생성할 때, 기존 테이블의 구조나 데이터를 기반으로 새로운 테이블을 만들 수 있습니다.
1.1 Customers 테이블을 cust_copy라는 이름으로 복사 (컬럼 이름 동일)
CREATE TABLE cust_copy
SELECT * FROM Customers;이 쿼리는 Customers 테이블의 모든 데이터를 cust_copy라는 테이블로 복사하여 동일한 구조와 데이터를 가진 새로운 테이블을 생성합니다.
1.2 Customers 테이블의 구조만 cust_blank라는 이름으로 생성 (컬럼 이름 동일)
CREATE TABLE cust_blank
SELECT * FROM Customers
WHERE 1 = 0;이 쿼리는 Customers 테이블의 구조만을 복사하여 cust_blank 테이블을 생성합니다. WHERE 1 = 0 조건을 사용하여 데이터를 복사하지 않고, 빈 테이블을 생성합니다.
1.3 Orders 테이블에서 1000달러 이상의 주문을 한 고객들의 정보를 big_orders라는 테이블로 생성
CREATE TABLE big_orders
SELECT customer_id, order_id, total_amount
FROM Orders
WHERE total_amount >= 1000;이 쿼리는 1000달러 이상의 주문을 한 고객들의 정보를 담은 새로운 테이블 big_orders를 생성합니다.
2. INSERT INTO (데이터 삽입)
서브 쿼리는 데이터를 삽입할 때도 매우 유용합니다. 특정 조건에 맞는 데이터를 다른 테이블에 삽입할 수 있습니다.
2.1 Orders 테이블에서 500달러 이상의 주문을 한 고객의 정보를 cust_blank에 삽입
INSERT INTO cust_blank
SELECT customer_id, first_name, last_name, email
FROM Customers
WHERE customer_id IN (SELECT customer_id FROM Orders WHERE total_amount >= 500);이 쿼리는 500달러 이상의 주문을 한 고객들의 정보를 cust_blank 테이블에 삽입합니다. 서브 쿼리를 사용해 Orders 테이블에서 조건에 맞는 고객 ID를 추출하고, 이를 기반으로 Customers 테이블에서 해당 고객들의 정보를 가져와 삽입합니다.
3. UPDATE (데이터 수정)
서브 쿼리는 데이터를 업데이트할 때도 조건을 세부적으로 설정하는 데 유용합니다.
3.1 Orders 테이블에서 평균 주문 금액보다 낮은 주문들의 총 주문 금액을 100달러 인상
UPDATE Orders
SET total_amount = total_amount + 100
WHERE total_amount < (SELECT AVG(total_amount) FROM Orders);이 쿼리는 평균 주문 금액보다 낮은 주문들을 찾아서 100달러씩 인상합니다. 서브 쿼리를 사용해 Orders 테이블의 평균 금액을 계산하고, 이를 기준으로 조건을 설정하여 데이터를 업데이트합니다.
4. DELETE (데이터 삭제)
서브 쿼리는 데이터 삭제에도 효과적으로 사용됩니다. 특정 조건에 맞는 데이터를 삭제할 수 있습니다.
4.1 Orders 테이블에서 평균 주문 금액보다 낮은 주문들을 삭제
DELETE FROM Orders
WHERE total_amount < (SELECT AVG(total_amount) FROM Orders);이 쿼리는 평균 주문 금액보다 낮은 주문들을 Orders 테이블에서 삭제합니다. 서브 쿼리로 평균 금액을 계산한 후, 그보다 낮은 금액을 가진 행들을 삭제하는 방식입니다.
서브 쿼리 활용 요약 🌟
- CREATE TABLE: 기존 테이블을 기반으로 새로운 테이블을 생성하거나 데이터를 조건에 맞춰 필터링하여 테이블을 생성하는 데 사용할 수 있습니다.
- INSERT INTO: 특정 조건에 맞는 데이터를 서브 쿼리를 통해 추출하고, 다른 테이블에 삽입할 수 있습니다.
- UPDATE: 서브 쿼리를 사용해 복잡한 조건을 설정하여 데이터를 효율적으로 업데이트할 수 있습니다.
- DELETE: 특정 조건을 만족하는 데이터를 서브 쿼리를 통해 찾고, 이를 삭제하는 데 사용할 수 있습니다.
서브 쿼리는 데이터를 생성, 수정, 삭제할 때 복잡한 조건을 처리하고 유연하게 데이터를 조작할 수 있는 강력한 도구입니다! 😊
