SQL Joins, CTEs, Subqueries, and Window Functions: A Practical Guide

INTRODUCTION

This article covers four techniques which are joins to combine tables, subqueries to break questions into stages, CTEs to keep those stages readable, and window functions to rank and compare rows without losing detail. Each comes with practical examples so you can start using them right away.

SQL JOINS

Joins are used to combine data from two or more tables based on a related column.
It helps in :

• Matching records using common columns.
• Improving data analysis by combining related information.
• Creating meaningful result sets from separate tables.

Types of SQL Joins

These are categorized into different types based on rows from two tables are matched and combined.
Consider the doctors and visits tables share a column doctor_id.

INNER JOIN

Also called JOIN
Used to retrieve rows where matching values exist in both tables.
Inner joins helps in:

• Combining records based on related column.
• Returning only matching rows from both tables.
• Excluding non-matching data from the result set.
• Ensuring accurate data relationships between tables.

Figure below shows inner join

select tableA.column1, tableA.column2, tableB.column1
from tableA as A
INNER JOIN tableB as B
ON A.matching_column = B.matching_column;
Example on the hospital data

SELECT * 
FROM doctors
INNER JOIN visits
ON doctors.doctor_id = visits.doctor_id;

Output of inner join query

LEFT JOIN

Also called LEFT OUTER JOIN.
Used to retrieve all rows from the left table and matching rows from the right table.
Useeful in :

• Returning all records from the left table.
• Showing matching data from the right table.
• Displays NULL values where no match exists.

Figure shows left join

Example using the hospital data doctors table and visits table

SELECT * 
FROM doctors
LEFT JOIN visits
   ON doctors.doctor_id = visits.doctor_id;

Left Join Output

RIGHT JOIN

Also called RIGHT OUTER JOIN
Used to retrieve all rows from the right table and matching rows from the left table.
Useful in:

• Returning all records from the right side table.
• Showing matching data from the left side table.
• Displays NULL values where no match exists in left table.

Figure show Right Join

Example using hospital dataset

SELECT patient_id, 
       patient_name, 
       visit_id
FROM patients as p
RIGHT JOIN visits as v
ON p.patient_id = v.patient_id;

Output of a Right Join

FULL JOIN

Used to combine the results of both left join and right join.
Helps in :

• Returning all rows from both tables.
• Showing matching records from each table.
• Displays NULL values where no match exists in either table.
• Provides complete data from both sides of the Join.

Figure shows Full Join

Example using hospital visits

SELECT * 
FROM patients
FULL JOIN visits
ON patients.patient_id = visits.patient_id;

Output of a Full Join

NATURAL JOIN

Combines two tables based on columns that have same name and compatible data types.
Returns only rows where the values in the common columns match.

• Joins tables using common columns with same name.
• Returns only rows where values in those columns match.
• The common column appears only once in the result.

Example using hospital data

SELECT patient_id, 
       patient_name, 
       visit_id, 
       visit_date
FROM patients
NATURAL JOIN visits;

Output of a Natural Join

SQL COMMON TABLE EXPRESSIONS(CTEs)

Common table expressions are a temporary set in SQL that you can reference within a single query.
CTEs simplify complex queries, makes them easier to read and can be reused multiple times within the same query.

CTEs are used for:

• Performing recursive operations.
• Breaking down multi-step calculations into manageable parts.
• Replacing nested subqueries in complex data retrieval tasks.

Consider the visits from the hospital visits table, lets find the average total_bill by insurance provider using
a CTE.

syntax

WITH avg_bill_by_insurance_provider AS
    (SELECT insurance_provider, AVG(total_bill) AS avg_bill
FROM visits
GROUP BY insurance_provider)
SELECT * 
FROM avg_bill_by_insurance_provider;

Output of CTE

WITH clause defines a CTE named avg_bill_by_insurance_provider.
The main query references this CTE to retrieve the average bill for each insurance provider.

syntax of a cte shown below

WITH cte_name as 
(SELECT query)
SELECT * 
FROM cte_name;

cte_name is a unique name for the CTE expression.
query _is a valid SQL query that returns a result set, which will be treated as a virtual table within the main query.
_select is the main query that can reference the CTE by it's name.

USES OF CTEs

Helps simplify and organize complex SQL queries.

• Breaks complex queries into small, reusable parts.
• Makes queries easier to read and understand.
• Helps work with hierarchial data using recursion.

LIMITATIONS

• Temporary : A CTE works only while the query runs, then it disappears.
• Performance: On a very large data, CTEs can be slower if reused many times.
• Restrictions : Some databases limit using CTEs with INSERT, UPDATE or DELETE.

SQL SUBQUERIES

Subquery is a query nested inside another SQL query.
It allows complex filtering, aggregation and data manipulation by using the result of one query inside another.
It is essential when we need to perform operations like:

• Apply Aggregate functions like SUM, COUNT, AVG dynamically.
• Update data using values from other tables.
• Delete rows based on conditions returned by another query. Example in the visit table to calculate the average total_bill of all patients.

SELECT * FROM visits
WHERE total_bill > (SELECT AVG(total_bill) FROM visits);

Subquery Output

The main query returns only the patients whose total_bill is higher than the average.

SQL clauses for Subqueries
WHERE _: Filters rows in the outer query based the results returned.
_HAVING : Filters grouped/ Aggregated results using values produced by Having clause.

Types of Subqueries

Single-Row Subqueries

A single-row subquery returns only one value as output.
Returns exactly one row as the result.
Commonly used with comparison operators such as =, >, <
.

Example using the patients table to find the oldest patient in the hospital visits data.

SELECT * 
FROM patients
WHERE patient_age = (SELECT MAX(patient_age) FROM patients);

Single row Subquery Output
The query returns the patient age whose age matches that of max value.

Multi-Row Subquery

• Returns more than one value.
• Returns multiple rows as result.
• Requires operators that handle multiple values such as IN, ANY or ALL.

Example using doctors and visits table in the hospital visits data to find doctors located in Kisumu city.

SELECT * 
FROM visits
WHERE doctor_id IN(
SELECT doctor_id FROM doctors WHERE  city = 'Kisumu');

Output

Correlated Subquery

This depends on the outer query for its value.
It's a dependant query it references columns from the outer query.
Executed once for each row of the outer query making it slower for large datasets.

Examples of using subqueries with clauses

Fetching Data Using Subquery in WHERE Clause

This example uses a subquery in the WHERE clause. The inner query finds doctor_id of doctors who were paid in 'Cash' in the visits table and the outer query displays their details from the doctor table.

Query

Output

CTE vs. Subqueries
| Feature | CTE | Subquery |
|-------------------------|------------------------------------------|-------------------------------------------|
| Referencing | Can be referenced multiple times | Typically used once |
| Readability | Improves readability for complex queries | Can become difficult to read when nested |
| Efficiency | Optimized for multiple references | May be less efficient for repeated operations |

WINDOW FUNCTIONS

These Functions allows performing calculations across a set of rows that are related to the current row, without collapsing the result into a single value.

Commonly used for tasks like:

• Aggregation
• Ranking
• Running totals

Uses OVER clause, which defines the 'Window' of rows for the calculation.

Window Function contains:

• PARTITION BY _ This divides the data into groups using partition by.
• ORDER BY _ Specifies the order of rows within each group using order by.

Functions like SUM(), AVG(), ROW NUMBER(), RANK(), and DENSE RANK() can be applied in a controlled way in window functions.

syntax

SELECT column_name1, 
    Window_function(column_name2)
OVER([PARTITION BY column_name3]
[ORDER BY column_name4]) AS new_column
FROM table_name;

Window_function - Function used for calculation or ranking
column_name1 - column to display
column_name2 - Function column
column_name3 - PARTITION BY column
column_name4 - ORDER BY column
new_column - result alias
table_name - source table

Types of Window Functions

There are two types namely:

1. Aggregate window functions.
2. Ranking window functions.

Examples using the hospital data.

Aggregate window functions.

Calculates aggregates over a window of rows while retaining individual rows.
These are:

• SUM() - sums values within a window.
• AVG() - calculates the mean of numbers.
• COUNT() - counts the rows.
• MAX() - returns the highest value.
• MIN() - returns the least value.

Using AVG(), to calculate average total_bill within each insurance provider in the visits table.

SELECT insurance_provider, total_bill, 
   AVG(total_bill) OVER (PARTITION BY insurance_provider) as average_totalbill
FROM visits;

Output

Ranking window functions.

Provides rankings of rows within a partition based on a specific criteria.
Ranking functions includes:

• RANK() - assigns ranks to rows, skips ranks for duplicates.
• DENSE RANK() - assigns ranks to rows, with duplicates included.
• ROW NUMBER() - assigns a unique number to each row in the result set.
• PERCENT RANK() - shows relative rank of a row as percentage between 0 and 1.

RANK()

Used to assign ranks to rows within a group based on a specific order
Commonly used to organize and analyze data.
Using RANK(), we will rank doctors by city.

Output

Fixing Window functions issues

When using window functions certain issues like slow performance can be avoided by:

• Partioning carefully - without partition by the whole table is treated as one group.
• Check order by - it controls the calculation order in the window.
• Optmise performance - on large datasets window functions might be slower, use indexes if needed.