0Pricing
Supabase Backend as a Service · 강의

고급 SQL 쿼리와 조인

정교한 데이터 세트를 가져오기 위해 다양한 유형의 조인, 서브쿼리, 윈도 함수가 포함된 복잡한 SQL 쿼리를 능숙하게 작성합니다.

고급 SQL 쿼리와 조인은(는) CoddyKit의 무료 Supabase Backend as a Service 강의입니다. 이것은 3개 중 1번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 Supabase Backend as a Service 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. Supabase Backend as a Service 강의에는 총 3개의 강의가 포함되어 있습니다.

이 강의의 일부는 아직 번역되지 않았으며 영어로 표시됩니다.

Beyond Basic Queries

Welcome to Advanced SQL Queries! So far, you've learned to select, filter, and sort data. But real-world applications often need to combine data from multiple sources or perform complex calculations.

This lesson will equip you with powerful techniques to retrieve sophisticated datasets, making your Supabase applications even smarter.

Joins Recap: Inner Join

Let's quickly refresh our memory on joins. A JOIN combines rows from two or more tables based on a related column between them.

An INNER JOIN returns only the rows where there is a match in both tables. If a row in one table doesn't have a match in the other, it's excluded.

Try running this example to set up our tables and see an INNER JOIN:

CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  department_id INT REFERENCES departments(department_id)
);

INSERT INTO departments (department_id, department_name) VALUES
(1, 'Sales'),
(2, 'Marketing'),
(3, 'Engineering'),
(4, 'HR');

INSERT INTO employees (employee_id, first_name, last_name, department_id) VALUES
(101, 'Alice', 'Smith', 1),
(102, 'Bob', 'Johnson', 2),
(103, 'Charlie', 'Brown', 1),
(104, 'Diana', 'Prince', 3),
(105, 'Eve', 'Adams', NULL);

SELECT
  e.first_name,
  e.last_name,
  d.department_name
FROM
  employees e
INNER JOIN
  departments d ON e.department_id = d.department_id;

Left Join: All from the Left

What if you want to see all employees, even those not assigned to a department yet? That's where LEFT JOIN (or LEFT OUTER JOIN) comes in handy.

A LEFT JOIN returns all rows from the 'left' table (the first one mentioned) and the matching rows from the 'right' table. If there's no match on the right, NULL values are returned for the right table's columns.

Run this to see employee Eve Adams, who has no department:

SELECT
  e.first_name,
  e.last_name,
  d.department_name
FROM
  employees e
LEFT JOIN
  departments d ON e.department_id = d.department_id;

Right Join: All from the Right

The opposite of a LEFT JOIN is a RIGHT JOIN (or RIGHT OUTER JOIN). It returns all rows from the 'right' table and matching rows from the 'left' table.

If there's no match on the left, NULL values are returned for the left table's columns. This is less common, as you can often rewrite it as a LEFT JOIN by swapping table order.

Let's find all departments, including 'HR' which currently has no employees:

SELECT
  e.first_name,
  e.last_name,
  d.department_name
FROM
  employees e
RIGHT JOIN
  departments d ON e.department_id = d.department_id;

Full Outer Join: Everything!

Want to see everything? A FULL OUTER JOIN (or just FULL JOIN) returns all rows when there is a match in either the left or the right table.

If a row doesn't have a match in the other table, the columns from the non-matching side will have NULL values. It's like combining a LEFT and a RIGHT join.

Observe how both Eve (no department) and HR (no employees) appear:

SELECT
  e.first_name,
  e.last_name,
  d.department_name
FROM
  employees e
FULL OUTER JOIN
  departments d ON e.department_id = d.department_id;

Self Join: Table to Itself

Sometimes, you need to join a table to itself. This is called a SELF JOIN and is useful for finding relationships within the same table, like 'employees and their managers'.

To do this, you use table aliases to treat the same table as two separate entities in your query.

Let's add a manager column and find out who manages whom:

ALTER TABLE employees
ADD COLUMN manager_id INT REFERENCES employees(employee_id);

UPDATE employees SET manager_id = 101 WHERE employee_id = 102;
UPDATE employees SET manager_id = 101 WHERE employee_id = 103;
UPDATE employees SET manager_id = 104 WHERE employee_id = 105;

SELECT
  E.first_name AS employee_name,
  M.first_name AS manager_name
FROM
  employees E
INNER JOIN
  employees M ON E.manager_id = M.employee_id;

Introducing Subqueries

Beyond joins, subqueries (also called inner queries or nested queries) are another powerful tool. A subquery is simply a SQL query nested inside a larger query.

They can be used to:

  • Filter data in a WHERE clause.
  • Define columns in a SELECT clause.
  • Create derived tables in a FROM clause.

Subqueries execute first, and their result is then used by the outer query.

Subqueries in WHERE Clause

A common use for subqueries is in the WHERE clause to filter results dynamically. You can use operators like IN, EXISTS, =, <, > with subqueries.

For example, let's find all employees who work in the 'Sales' department without knowing the department_id beforehand:

SELECT
  first_name, last_name
FROM
  employees
WHERE
  department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name = 'Sales'
  );

Scalar Subqueries in SELECT

A scalar subquery is a subquery that returns a single value (one row, one column). These are often used in the SELECT clause to add a calculated value to each row of the main query.

Let's find each employee's name and also show the total number of employees in their department. This demonstrates how a subquery can compute a value for each row.

SELECT
  e.first_name,
  e.last_name,
  d.department_name,
  (SELECT COUNT(*)
   FROM employees
   WHERE department_id = e.department_id) AS dept_employee_count
FROM
  employees e
LEFT JOIN
  departments d ON e.department_id = d.department_id;

Advanced Queries Challenge

Consider a scenario where you want to list all departments, and for each department, show the names of employees working there. If a department has no employees, it should still appear in the list with NULL for employee names.

Which SQL JOIN type is most appropriate for this task?

Recap: Your SQL Superpowers

You've gained some serious SQL superpowers today!

  • Joins: Beyond INNER JOIN, you learned about LEFT, RIGHT, and FULL OUTER JOINs to handle different data inclusion needs.
  • Self Join: How to join a table to itself for hierarchical data.
  • Subqueries: Nesting queries to filter data (WHERE clause) or compute scalar values (SELECT clause).

These techniques are fundamental for building powerful and flexible data retrieval logic in your Supabase projects. Keep practicing!

자주 묻는 질문

“고급 SQL 쿼리와 조인” 강의는 무료인가요?

네 — “고급 SQL 쿼리와 조인” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 Supabase Backend as a Service 강의 전체를 잠금 해제할 수 있습니다. Supabase Backend as a Service 강의에는 총 3개의 강의가 포함되어 있습니다.

“고급 SQL 쿼리와 조인”에서 뭘 배우나요?

정교한 데이터 세트를 가져오기 위해 다양한 유형의 조인, 서브쿼리, 윈도 함수가 포함된 복잡한 SQL 쿼리를 능숙하게 작성합니다. 브라우저에서 직접 실행하는 실습 코드로 Supabase Backend as a Service을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

Supabase Backend as a Service을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 Supabase Backend as a Service은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 3개 중 1번째 강의입니다.

“고급 SQL 쿼리와 조인” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 Supabase Backend as a Service 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 Supabase Backend as a Service 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

  1. 고급 SQL 쿼리와 조인
  2. 성능을 위한 데이터베이스 인덱싱
  3. 데이터베이스 함수와 트리거
← Supabase Backend as a Service(으)로 돌아가기