PostgreSQL 조인 및 윈도우 함수

Source: Dev.to

Understanding JOINS in PostgreSQL

Joins let you merge data from multiple tables (or views) by linking them through related columns.
The choice of join type depends mainly on:

• Which rows you want to keep (including unmatched ones)
• How the tables relate to each other

Main Join Types

CROSS JOIN

Creates the Cartesian product – every row from the first table pairs with every row from the second. No ON clause needed.

SELECT p.project_name, e.name, e.salary
FROM sales_data.projects p
CROSS JOIN sales_data.employees e;

INNER JOIN

Returns only matching rows from both tables. Non‑matching rows are excluded.

SELECT emp.name, dep.department_name
FROM sales_data.employees emp
INNER JOIN sales_data.departments dep
  ON emp.department_id = dep.department_id;

LEFT JOIN (LEFT OUTER JOIN)

Keeps all rows from the left table, plus matching rows from the right.

SELECT *
FROM sales_data.projects p
LEFT JOIN sales_data.employees e
  ON p.employee_id = e.employee_id;

RIGHT JOIN (RIGHT OUTER JOIN)

Keeps all rows from the right table, plus matching rows from the left.

SELECT *
FROM sales_data.projects p
RIGHT JOIN sales_data.employees e
  ON p.employee_id = e.employee_id;

FULL JOIN (FULL OUTER JOIN)

Returns all rows from both tables. Places NULL where no match exists.

SELECT *
FROM sales_data.projects p
FULL JOIN sales_data.employees e
  ON p.employee_id = e.employee_id;

Enhancing Joins

Add filtering, sorting, etc.:

SELECT *
FROM sales_data.projects p
FULL JOIN sales_data.employees e
  ON p.employee_id = e.employee_id
WHERE e.employee_id < 4
ORDER BY e.employee_id ASC NULLS LAST,
         p.project_name ASC;

Window Functions in PostgreSQL

Window functions compute values over a “window” of rows related to the current row — without collapsing rows like GROUP BY does.

Ranking Functions

• ROW_NUMBER() – assigns unique, consecutive numbers (1, 2, 3, …)
• RANK() – same values get the same rank, but skips numbers after ties (1, 1, 3, …)
• DENSE_RANK() – same values get the same rank, no skips (1, 1, 2, …)
• NTILE(n) – divides rows into n roughly equal buckets (useful for quartiles, percentiles)

SELECT *,
       RANK()       OVER (ORDER BY salary DESC NULLS FIRST) AS rank_col,
       DENSE_RANK() OVER (ORDER BY salary DESC NULLS FIRST) AS dense_rank_col,
       ROW_NUMBER() OVER (ORDER BY salary DESC NULLS FIRST) AS row_num
FROM sales_data.working_hub;

Aggregate Functions in Windows

Run aggregates (SUM, AVG, COUNT, MIN, MAX, …) across the window without grouping.

SELECT *,
       SUM(e.salary) OVER () AS total_salary_company_wide
FROM sales_data.projects p
FULL JOIN sales_data.employees e
  ON p.employee_id = e.employee_id;

Navigation / Value Functions

Compare rows within the ordered window:

• LAG(col) – value from the previous row
• LEAD(col) – value from the next row
• FIRST_VALUE(col) – first value in the window
• LAST_VALUE(col) – last value in the window

SELECT *,
       LAG(salary)  OVER (ORDER BY salary DESC) AS prev_salary,
       LEAD(salary) OVER (ORDER BY salary DESC) AS next_salary
FROM sales_data.working_hub;

PARTITION BY

Splits data into groups (like GROUP BY), but keeps all rows intact.

SELECT *,
       SUM(e.salary) OVER (PARTITION BY p.project_name) AS salary_per_project
FROM sales_data.projects p
FULL JOIN sales_data.employees e
  ON p.employee_id = e.employee_id;