JOINS AND WINDOW FUNCTIONS IN SQL

The JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Here are the different types of JOINs in SQL:

(INNER) JOIN: Returns only rows that have matching values in both tables
LEFT (OUTER) JOIN: Returns all rows from the left table, and only the matched rows from the right table
RIGHT (OUTER) JOIN: Returns all rows from the right table, and only the matched rows from the left table
FULL (OUTER) JOIN: Returns all rows when there is a match in either the left or right table

SQL INNER JOIN

INNER JOIN is used to retrieve rows where matching values exist in both tables. Combining records based on a related column. Returning only matching rows from both tables. Excluding non-matching data from the result set. Ensuring accurate data relationships between tables
Only returns rows where there's a match in BOTH tables. If a customer has no orders, they don't show up. If an order has no customer, it doesn't show up either.

SELECT customers.name, orders.product
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

The result of this will be; only the customers who placed an order will appear

LEFT(OUTER) JOIN

LEFT JOIN is used to retrieve all rows from the left table and matching rows from the right table. Returns ALL rows from the left table, plus any matching rows from the right table. If there's no match on the right side, you still get the left row just with NULL in the right columns.

SELECT customers.name, orders.product
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

The results of this is that the customers will appear and those that will have no order will have a NULL under 'product'.

RIGHT (OUTER) JOIN

RIGHT JOIN is used to retrieve all rows from the right table and the matching rows from the left table. It helps in:

Returns ALL rows from the right table, and matching rows from the left. In practice, most people rewrite this as a LEFT JOIN by swapping the table order it's easier to reason about.

SELECT customers.name, orders.product
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id;

The results; All orders will appear, even after deletion of customer from system.

FULL (OUTER) JOIN

FULL JOIN is used to combine the results of both LEFT JOIN and RIGHT JOIN. Returns ALL rows from BOTH tables. Where there's no match, you get NULLs on the missing side. Use this when you need a complete picture of both datasets regardless of matches.

SELECT customers.name, orders.product
FROM customers
FULL OUTER JOIN orders ON customers.id = orders.customer_id;

Window Functions in SQL

A window function lets you perform calculations across a group of rows while still keeping every individual row in your result.

The Basic Syntax
The window function follows this pattern:

function_name()  OVER  (
    PARTITION BY  column - group rows (optional)
    ORDER BY      column -set row order  (optional)
)

PARTITION BY is like GROUP BY it resets the calculation per group. ORDER BY defines the order rows are processed within that group.

The 5 Most Useful Window Functions

ROW_NUMBER() Number Your Rows

Assigns a unique sequential number to each row within a partition. Restarts at 1 for each new group.

SELECT name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

RANK() Handle Ties Fairly

Like ROW_NUMBER() but handles ties: two employees with equal salary both get rank 1, and the next gets rank 3 (skips 2). Use DENSE_RANK() if you don't want the gap.

SELECT name, score,
  RANK()        OVER (ORDER BY score DESC) AS rank_with_gap,
  DENSE_RANK()  OVER (ORDER BY score DESC) AS rank_no_gap
FROM students;

SUM() OVER Running Totals

Calculates a cumulative sum as it moves down the rows. One of the most used window functions in business analytics.

SELECT sale_date, amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

Result: each row shows the day's amount AND a growing total. By the last row it equals the grand total.

LAG() Look at the Previous Row

Grabs a value from a previous row without needing a selfjoin. Perfect for month-over-month or day-over-day comparisons.

SELECT month, revenue,
  LAG(revenue, 1) OVER (ORDER BY month)              AS prev_month,
  revenue LAG(revenue, 1) OVER (ORDER BY month)    AS change
FROM monthly_sales;

LEAD() does the opposite it looks at the next row. Useful for forecasting or "days until next event" calculations.

AVG() OVER Compare to Group Average

Shows each row's value alongside the average for its group something impossible with plain GROUP BY.

SELECT name, department, salary,
  AVG(salary) OVER (PARTITION BY department)  AS dept_avg,
  salary AVG(salary) OVER (PARTITION BY department) AS vs_avg
FROM employees;