In this article, we will cover the basic concepts of SQL joins and window functions. We assume that you are already familiar with the fundamentals of Data Definition Language (DDL): creating schemas and tables, understanding data types, and applying constraints.
We will explore how joins allow you to combine and relate data from multiple tables, and how window functions let you perform calculations across a set of rows related to the current row, without collapsing the result set. Examples of window functions include computing running totals, rankings, moving averages, and cumulative sums. By the end of this article, you’ll understand when and how to use joins and window functions effectively in real-world SQL queries.
What are SQL Joins?
As the name suggests, a join refers to the process of combining two or more items. In SQL, joins are used to combine two or more tables into a single result set using a common field that exists in all the tables involved.
Interestingly, in SQL, this concept can also extend to a table joining itself, which is useful in hierarchical or relational scenarios.
We will explore the different types of joins with practical examples:
- When to use them
- How to use them
- The syntax for each type
We will use four tables: customers, products, sales, and inventory to demonstrate SQL joins, with each table containing relevant data on customers, products, transactions, and stock.
The main types of SQL joins are:
- INNER JOIN
When to use: When you want only matching records from two tables.
How to use: Combine two tables on a common column (foreign key).
Syntax & Example: Get a list of all sales with customer and product details:
- LEFT JOIN
When to use: When you want all records from the main table and optional matches from another table.
How to use: Use LEFT JOIN when the left table is the main table.
Syntax & Example: List all customers and their purchases, even if they haven’t bought anything:
- RIGHT JOIN
When to use: When you want all records from the right table, even if no match exists in the left table.
How to use: Right join is basically the reverse of LEFT JOIN.
Syntax & Example: List all products and any sales made, even if a product hasn’t been sold:
- FULL OUTER JOIN
When to use: When you want all records from both tables, matched where possible.
How to use: Combine LEFT JOIN and RIGHT JOIN behavior.
Syntax & Example: List all sales and all inventory items, even if some products haven’t been sold:
- CROSS JOIN
When to use: When you need all combinations of rows from two tables.
How to use: CROSS JOIN does not require a condition.
Syntax & Example: Get every possible combination of customers and products (maybe for a promotion plan):
- SELF JOIN
When to use: When a table needs to relate to itself, like finding hierarchical relationships.
How to use: Alias the table twice and join on self-referencing columns.
Example (customers with the same first name):
What are Window Functions?
Window functions are honestly one of the most powerful (and underrated) tools in SQL.
They let you do calculations across a bunch of rows that are “related” to the current row… but without smashing everything down into one aggregated row like a normal GROUP BY would. You still get every single row back just with extra useful columns added on.
Core idea in one sentence:
Window functions calculate stuff over a “window” of rows defined by you, while keeping all the original rows intact.
The Big Differences from Regular Aggregates
SUM(), AVG(), COUNT() etc. with GROUP BY one row per group
SUM(), AVG(), COUNT() etc. with OVER() still one row per original row, but now you get the aggregate value repeated/calculated per window
The Must-Know Window Functions
ROW_NUMBER() - gives every row a unique 1,2,3… number
RANK() - like sports rankings (1,2,2,4 if ties)
DENSE_RANK() - same but no gaps (1,2,2,3)
SUM() / AVG() / COUNT() over a window - running totals, moving averages, etc.
LEAD() / LAG() - peek at the next or previous row’s value
NTILE(n), PERCENT_RANK(), CUME_DIST() - more niche but handy for percentiles and bucketing.
explore further on Window functions here.
Conclusion
In this article, we explored the fundamentals of SQL joins and window functions, two powerful tools for combining and analyzing data. Joins allow you to merge tables based on relationships, while window functions let you perform advanced calculations across related rows without losing detail. Mastering these techniques enables data engineers to write efficient queries, uncover insights, and build robust analytics pipelines. By applying joins and window functions to real-world tables like customers, products, sales, and inventory, you can transform raw data into actionable insights with clarity and precision.
Top comments (0)