Understanding Joins and Window Functions in SQL

Introduction

In this article, we will explore two of the most powerful and widely used features in SQL: JOINs and Window Functions. We will begin by understanding what they are and how they work, and then walk through practical examples to see when, where, and why they are used in real-world scenarios.

Let's start with joins:

What are Joins?

In Structured Query Language (SQL), a join is a clause used to combine rows from two or more tables based on a related column between them. The purpose of a joins is to retrieve data that is spread across multiple tables into a single table, providing a unified view.
Example: In database with an orders and customers table, a join can be used to answer questions such as which customer placed an order.

Type of Joins

1. INNER JOIN

It combines two or more tables based on a specified common column with matching values and only returns the set of records that have a match in all the involved tables. The rows that don't have a match in the other table(s) are excluded from the result set.

Example:

Above, are tables customers and orders

select  first_name, last_name
from article.customers
inner join article.orders on customers.customer_id = orders.customer_id;

Using INNER JOIN based on the specified column customer_id on both the customers and orders table, we are able to join the tables, getting a result of only the records which have customer_ids on both tables.

2. LEFT JOIN

It retrieves all rows from the left (first) table and matching rows from the right (second) table. If a row in the left table has no corresponding match in the right table based on the join condition, the result will contain NULL values for the columns of the right table.

Example:
Using our previous tables

--LEFT JOIN
select first_name, last_name, order_date
from article.customers
left join article.orders on customers.customer_id = orders.customer_id;

We perform a left join on the orders table with the customers table based on the customer_id condition. The query returns all the customers but some with the NULL value on the date column as they are not on the orders table(they have never made an order).

The left join is also known as the left outer join.

3. RIGHT JOIN

It retrieves all rows from the right (second) table and matching rows from the left (first) table. If a row in the right table has no corresponding match in the left table based on the join condition, the result will contain NULL values for the columns of the left table.

--RIGHT JOIN
select first_name, last_name, order_date
from article.customers
right join article.orders on customers.customer_id = orders.customer_id;

Now, on performing the RIGHT JOIN on our tables, we get a result of all the rows from the right table (orders) that have a match on the left table (customers). Compared to the LEFT JOIN, we lose the NULL values as those rows don't have match in the left table (customers).

The right join is also known as the right outer join.

4. FULL OUTER JOIN

It returns all rows from both the left and right tables, combining matching records and using NULL values for columns where there is no match

Example:

-- FULL OUTER JOIN
select first_name, last_name, email, phone_number,order_id, order_date, book_id
from article.customers
full outer join article.orders on customers.customer_id = orders.order_id;

When we perform a FULL OUTER JOIN on the customers and orders tables, the SQL query returns all the rows from both tables and filling NULL values for there is no match.

Now, let's look at window functions:

What are Window Functions?

In SQL, Window functions perform calculations across a set of table rows related to the selected row without merging these rows into a single output/value. Unlike traditional output functions such as SUM() and COUNT() which reduce multiple rows to one, window functions return a value for each row in the original result set.
They operate over a window (specific set of rows) defined by the OVER() clause.

Key Components of SQL Window Functions

Select: This defines the columns you want to select from the table_name(The columns you select, create your window).
Function: This is the window function you want to use.
Over Clause: This defines the partitioning and ordering of rows and can be applied with functions to compute aggregated values.
Partition by: This divides rows into partitions based on specified expressions. It is suitable for large datasets because it makes them simpler to manage.
Order by: This is a specified order expression to define the order in which rows will be processed within each partition.
Output column: This is the name you give to your output column.

Types of window functions in SQL

1. Aggregate window functions: They calculate aggregates over a window of rows while retaining individual rows.
2. Ranking window functions: They provide rankings of rows within a partition based on a specific criteria.
3. Value window functions: They are used to assign to rows, values from other rows. It is usually possible to replicate the values of these functions using two nested queries, hence they are not that common compared to aggregate and ranking window functions.

Example:

We have a table employees containing information of different employees from different departments.

Let's use a ranking window function RANK() (which provides a unique rank to each row while skipping duplicates), an OVER() to define the partitioning and ordering of rows using PARTITION BY and ORDER BY then define an output column that will contain out results.

In our table, we have used the RANK() function to rank employees by department. We have applied (PARTITION BY department) to partition our results in the different departments in our table, (ODER BY salary DESC) to order the results by descending salary value then we have output our RANK() using rank_by_salary column.

After the ranking of the Finance department is over, we can see a new ranking starting for the HR department.

Here we are now using the aggregate window function AVG() to get the average salary for each department and comparing it to each of the employees salary in different departments

To learn more on the other window functions, click here Window functions

Conclusion

From this article, we have gone through and understood what are joins and window functions are. Through clear explanations and practical, real-world examples, you’ve learned what they are, the different types available, how they work, and exactly how to write clean, efficient SQL queries to achieve your desired results. Mastering these powerful tools will dramatically improve your ability to analyze, transform and retrieve data with precision. Start applying them in your own projects today, the more you practice, the more natural they will feel.
Happy querying!