Two of the most powerful SQL concepts a data person, like a data analyst or a backend developer, will use are "Joins" and "Window Functions." "Joins" are used to combine data from multiple tables, and "Window Functions" are used to perform complex calculations on related rows of data without aggregating the results.
In this article, I will explain what "Joins" and "Window Functions" are, along with examples, in a very simple way.
Part 1: SQL Join
In relational databases, data is often stored in multiple tables. For example, you might have:
• customers table
• orders table
Each table stores different information, but they are connected using a common key like customer_id.
What Is a Join?
A JOIN combines rows from two or more tables based on a related column.
Think of it like matching records from one Excel sheet to another using a common ID
Types of Joins
1. INNER JOIN
Returns only the matching records in both tables.
SELECT
c.customer_id,
c.name,
o.order_id,
o.order_date
FROM customers c
INNER JOIN orders o
ON c.customer_id=o.customer_id;
This query returns customers who have placed orders.
2. LEFT JOIN
Returns all records from the left table and matching records from the right table. If there is no match, NULL values appear.
SELECT
c.customer_id,
c.name,
o.order_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;
This shows all customers, even those who haven’t placed any orders.
3. RIGHT JOIN
Returns all records from the right table and matching ones from the left.
SELECT
c.customer_id,
c.name,
o.order_id
FROM customers c
RIGHT JOIN orders o
ON c.customer_id = o.customer_id;
4. FULL OUTER JOIN
Returns all records from both tables, whether they match or not.
SELECT
c.customer_id,
c.name,
o.order_id
FROM customers c
FULL OUTER JOIN orders o
ON c.customer_id = o.customer_id;
How I Understand Joins
Joins are about relationships.
If two tables share a key, a join lets you bring their data together into one meaningful result.
Without joins, working with relational databases would be extremely limited.
Part 2: Window Functions
Window functions are more advanced and extremely powerful.
Unlike GROUP BY, window functions do not collapse rows. Instead, they perform calculations across a set of rows related to the current row.
That “set of rows” is called a window.
Basic Syntax
FUNCTION_NAME() OVER (
PARTITION BY column
ORDER BY column
)
•PARTITION BY → splits data into groups
•ORDER BY → defines row order inside each group
Common Window Functions
1. ROW_NUMBER()
Assigns a unique number to each row.
SELECT
customer_id,
order_id,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC
) AS row_num
FROM orders;
This ranks each customer’s orders from newest to oldest.
2. RANK()
Assigns ranking, but allows ties.
SELECT
product_id,
revenue,
RANK() OVER (
ORDER BY revenue DESC
) AS revenue_rank
FROM products;
3. SUM() as a Window Function
SELECT
customer_id,
order_date,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS running_total
FROM orders;
This calculates a running total per customer.
GROUP BY vs Window Functions
GROUP BY reduces rows.
Window functions keep all rows and add calculated columns.
Example using GROUP BY:
SELECT
customer_id,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;
Example using Window Function:
SELECT
customer_id,
amount,
SUM(amount) OVER (PARTITION BY customer_id) AS total_spent
FROM orders;
The second query keeps each order visible while still showing total spent.
Why Joins and Window Functions Matter
If you’re working in analytics (Power BI, Excel, SQL Server, PostgreSQL, etc.), these two skills are essential:
•Joins help you combine datasets.
•Window functions help you analyze patterns within those datasets.
In real-world analysis:
•You join sales with customers.
•You calculate rankings.
•You create running totals.
•You detect duplicates.
•You identify top-performing products.
These are daily tasks for data analysts and backend engineers.
Summary of Key Points
1.Joins combine data from multiple tables using related columns.
2.INNER JOIN returns matching records only.
3.LEFT JOIN keeps all records from the left table.
4.Window functions perform calculations without grouping rows.
5.PARTITION BY divides data into logical groups.
6.Window functions are powerful for ranking, running totals, and advanced analytics.
If you truly understand Joins and Window Functions, you’ve crossed an important milestone in SQL mastery.
Joins teach you how data connects.
Window functions teach you how data behaves.
Master both — and you unlock advanced analytics capabilities that tools like Power BI and Tableau rely on under the hood.
Top comments (0)