PostgreSQL Joins and Window Function

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.

Example result: 5 rows × 5 rows = 25 rows.SQLSELECT 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.

1. Ranking Functions

ROW_NUMBER() — assigns unique, consecutive numbers (1,2,3,… no ties)
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 (good for quartiles, percentiles)

SQLSELECT *,
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;

1. Aggregate Functions in Windows
   Run aggregates (SUM, AVG, COUNT, MIN, MAX…) across the window without grouping.
   SQLSELECT *,
   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;

2. Navigation / Value Functions
   Compare rows within the ordered window:

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

SQLSELECT *,
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;

1. PARTITION BY Splits data into groups (like GROUP BY), but keeps all rows intact. SQLSELECT *, 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;