SQL Joins & Window Functions

JOINS

They allow us to work with multiple tables and allows us to join data in different tables. Joins happen when there's is a primary and foreign keys-they allow us to reference from our tables uniquely. There are diff types of joins. Below briefly explains the most common ones.

• Inner Join -Returns only the rows that have matching values in both tables.

select *
from sales s
inner join products p
on s.product_id = p.product_id

That's generally the syntax for joins across the diff types of joins. The first tables(sales) is the left table and the other the right - this part will be important when we dive into other types of joins. In this case what will be returned will be rows with product_id in both tables. s.product_id is the primary key while the p.product_id id the foreign key.

• Left Join - Returns all rows in the left table and match rows from right table. Syntax is similar to inner join bar instead of inner its left
  select *
from sales s
left join products p
on s.product_id = p.product_id

• Right Join - Returns all rows in the right table and match rows from left table. Syntax is similar to inner join bar instead of inner its left
  select *
from sales s
right join products p
on s.product_id = p.product_id

The most common joins are inner join and left join. Other types of joins include full join which returns all rows from the right and left table. We also have a self-join which is used to join a table to itself using diff aliases

WINDOW FUNCTIONS

A window function performs a calculation across a group of rows that are related to the current row. This group of rows is called a window. Unlike aggregate functions, window functions do not collapse rows into a single result. Instead, they return a value for every row. See below general syntax


function_name() OVER (
PARTITION BY column
ORDER BY column
)


• function_name() - the calculation (ROW_NUMBER, RANK, SUM, DENSE_RANK)
• OVER () → defines the window
• PARTITION BY - splits data into groups
• ORDER BY - determines calculation order within the group

Below are some common window functions:

• ROW_NUMBER () - Assign unique ranking
• RANK () - Ranking with gaps
• DENSE_RANK () - Ranking without gaps
• SUM () - Running totals
• LAG () - Access previous row
• LEAD () - Access next row

SQL window functions are powerful tools that extend the capabilities of SQL beyond simple aggregation. By allowing calculations across related rows while preserving the dataset structure, they make tasks like ranking, running totals, and comparisons much easier for analysts.