DEV Community

Cover image for A Guide to SQL Joins and Window Functions
Victoria Joy
Victoria Joy

Posted on

A Guide to SQL Joins and Window Functions

#database #sql #programming #datascience

Databases are used to store data in tables. A database may have multiple tables, with common columns. Joins in SQL are used to combine these tables, more like stitching together pieces of related data from different tables.

Consider a company’s database with the following tables: Employees, Departments and Projects. We will use this to discuss the types of joins.
The Employees table has the following columns:

Table showing Employees schema with columns for EmployeeID, FirstName, LastName, DepartmentID, and Salary
The departments table has the following columns:

Table showing Departments schema with columns for DepartmentID and DepartmentName

The Projects table has the following columns:

Table showing Projects schema with columns for ProjectID, ProjectName, and DepartmentID

INNER JOIN
Returns the rows that match in both tables.

Venn diagram showing the intersection of two sets representing an INNER JOIN

LEFT JOIN
Returns all rows from the left table and the matching rows from the right table.

Venn diagram with the entire left circle shaded representing a LEFT JOIN

RIGHT JOIN
Returns all rows from the right table and the matching rows from the left table.

Venn diagram with the entire right circle and the overlapping middle section shaded representing a RIGHT JOIN

FULL OUTER JOIN
Combines the left and right join, then shows all rows from the tables. If there are no matches, it uses NULLS.

Venn diagram with both circles entirely shaded representing a FULL OUTER JOIN

CROSS JOIN
Returns a combination of rows from both tables.

Graphic showing a Cartesian Product where every row of one table is connected to every row of another table

SELF JOIN
Joins a table to itself.

Graphic illustrating a table being joined to a copy of itself using an alias

SQL WINDOW FUNCTIONS

Allows you to perform mathematical calculations across a set of rows.
Consider an Sales database with the following tables: Customers, Orders and Books. We will use this to discuss the window functions.

1. ROW_NUMBER() - Assigns a unique integer to rows starting from 1 based on the order specified by the ORDER BY clause.

Table showing sequential row numbers 1 through 5 assigned to rows regardless of duplicate values

2. RANK() - Assign the same rank when there are ties and skips. It leaves a gap.

Table showing RANK() logic where tied values get the same rank and the next rank is skipped (1, 2, 2, 4)

3. DENSE_RANK() - Assigns ranks, but does not leave a gap in the ranking.

Table showing DENSE_RANK() logic where tied values get the same rank but the next rank is not skipped (1, 2, 2, 3)

4. LEAD()- Access data from the next row. It is used to track trends.

Example of LEAD() function pulling a value from the following row into the current row to compare trends

5. LAG() - Access data from the previous row.

Example of LAG() function pulling a value from the previous row into the current row for comparison

6. NTILE()- Divide the result set into a specified number of roughly equal parts.

Diagram showing a dataset divided into four equal quartiles using NTILE(4)

Top comments (0)