Introduction

SQL is more than retrieving data from a database. It also helps us combine information from multiple tables, analyse trends, rank records, calculate running totals, and break down complex queries into manageable steps.

In this article, we will explore four essential SQL concepts: joins, window functions, Common Table Expressions (CTEs), and subqueries. Using a hospital database implemented in PostgreSQL, we will examine how each concept works, when to use it, and how it can help answer practical questions.

The hospital dataset contains information about patients, doctors, departments, diagnoses, procedures, wards, and hospital visits. It allows us to explore questions such as which department has the highest billing, which patients have multiple visits, and how individual bills compare with group averages.

Target audience

This article is intended for:

  • Beginners learning SQL and relational databases.
  • Students completing database and data analytics assignments.
  • Aspiring data analysts who want practical SQL experience.
  • Developers who want to write clearer and more effective SQL queries.

Prerequisites

Before following the examples, you should have:

  • Basic knowledge of SQL statements such as SELECT, FROM, WHERE, and ORDER BY.
  • A basic understanding of relational database tables, primary keys, and foreign keys.
  • Access to PostgreSQL or a PostgreSQL-compatible environment.
  • The hospital database script used in this article.

Learning objectives

By the end of this article, you should be able to:

  1. Explain and use different types of SQL joins.
  2. Apply window functions to rank records, compare visits, and calculate aggregates.
  3. Understand OVER(), PARTITION BY, window ordering, and window frames.
  4. Create basic, multiple, and recursive CTEs.
  5. Use scalar, correlated, non-correlated, and nested subqueries.
  6. Apply IN, EXISTS, ANY, and ALL.
  7. Decide when to use a CTE, join, window function, or subquery.

Understanding the hospital database

The database uses the PostgreSQL schema morning.

The main tables include:

Table Description
morning.patients Stores patient names, ages, genders, counties, and sub-counties.
morning.doctor Contains doctor names, specialties, genders, and experience.
morning.department Stores hospital department names and IDs.
morning.diagnosis Contains diagnosis names and categories.
morning.procedure Stores medical procedure names and categories.
morning.words Stores ward IDs and ward names.
morning.visit_flat Contains visit dates, patient and doctor IDs, diagnoses, procedures, admission details, bills, and satisfaction scores.

The dataset contains 30 hospital visits, 25 patients, four doctors, four departments, 18 diagnoses, 15 procedures, and four wards.

Most examples use morning.visit_flat because it contains the records for individual hospital visits.

One important implementation detail is that the supplied script names the ward table morning. wards. The examples retain that exact table name. Also, the script does not declare foreign-key constraints for doctor_id and department_id, although the IDs match the corresponding records in the supplied data.

1. SQL Joins: Combining Data from Multiple Tables

A database often stores related information in separate tables to reduce duplication and improve organization. Joins allow us to retrieve related information using matching columns.

For example, the patient's name is stored in patients, while the visit date and bill are stored in visit_flat. A join allows us to display this information together.

INNER JOIN

An INNER JOIN returns only records that have matching values in both tables.

Example 

SELECT p.patient_name, v.visit_id, v.visit_date, v.total_bill_kes FROM morning.patients AS p INNER JOIN morning.visit_flat AS v ON p.patient_id = v.patient_id ORDER BY v.visit_id;

Explanation

  • patients provides the patient's name.
  • visit_flat provides the visit ID, date, and bill.
  • The ON clause matches records using patient_id.

Expected result

The query returns 30 rows because all 30 visits have matching patient records.

For example, visit 1001 belongs to Amina Otieno, occurred on 1 June 2026, and has a recorded bill of KSh 3,500.

When should you use INNER JOIN? Use it when you only want records with matching data in both tables.

LEFT JOIN

A LEFT JOIN returns every record from the left table and matching records from the right table. If no match exists, the right-side columns contain NULL.

Example 

SELECT p.patient_id, p.patient_name, v.visit_id, v.visit_date FROM morning.patients AS p LEFT JOIN morning.visit_flat AS v ON p.patient_id = v.patient_id ORDER BY p.patient_id, v.visit_date;

Explanation

Every patient is retained, even if they have no visit record. Patients with multiple visits appear more than once.

In the supplied dataset, every patient has at least one recorded visit, so no patient has NULL visit details.

To find patients without recorded visits, we can use:

SELECT p.patient_id, p.patient_name FROM morning.patients AS p LEFT JOIN morning.visit_flat AS v ON p.patient_id = v.patient_id WHERE v.visit_id IS NULL;

This returns zero rows for the current dataset.

When should you use LEFT JOIN? Use it when all records from the left-hand table must be retained, including those without matching records on the right.

RIGHT JOIN

A RIGHT JOIN returns every record from the right table and matching records from the left table.

Example 

SELECT p.patient_name, v.visit_id, v.visit_date, v.total_bill_kes FROM morning.patients AS p RIGHT JOIN morning.visit_flat AS v ON p.patient_id = v.patient_id ORDER BY v.visit_id;

Explanation

Every visit is retained. If a visit referred to a patient who did not exist in the patients table, the patient columns would contain NULL.

In the supplied dataset, all visit patient IDs have matching patient records, so all 30 visits display a patient name.

When should you use RIGHT JOIN? Use it when every record from the right-hand table must be retained. The same result can often be expressed with a LEFT JOIN by reversing the table order.

FULL OUTER JOIN

A FULL OUTER JOIN returns all matching records and preserves unmatched records from both tables.

Example 

SELECT p.patient_id, p.patient_name, v.visit_id, v.total_bill_kes FROM morning.patients AS p FULL OUTER JOIN morningvisit_flat AS v ON p.patient_id = v.patient_id ORDER BY p.patient_id, v.visit_id;

Explanation

  • Matching patient and visit records are combined.
  • Patients without visits would still appear, with visit columns set to NULL.
  • Visits without matching patients would also appear, with patient columns set to NULL.

The supplied dataset has no unmatched patient-to-visit records, so the query returns 30 rows.

When should you use FULL OUTER JOIN? Use it when comparing or reconciling datasets where either side may contain unmatched records.

CROSS JOIN

A CROSS JOIN returns every possible combination of rows from two tables.

Example 

SELECT d.doctor_name, dep.department_name FROM morning.doctor AS d CROSS JOIN morning. department AS dep ORDER BY d.doctor_id, dep.department_id;

Explanation

The dataset contains four doctors and four departments. The query generates every possible doctor-department combination.

The expected number of rows is:

4 doctors × 4 departments = 16 combinations.

These combinations are generated for demonstration; they do not represent confirmed doctor assignments.

When should you use CROSS JOIN? Use it when you need every possible combination, such as generating combinations for testing or analysis.

SELF JOIN

A self join joins a table to itself. It allows us to compare records within the same table.

Example: Find patients from the same county 

SELECT p1.patient_name AS patient_one, p2.patient_name AS patient_two, p1.county FROM morning.patients AS p1 INNER JOIN morning.patients AS p2 ON p1.county = p2.county AND p1.patient_id < p2.patient_id ORDER BY p1.county, p1.patient_id;

Explanation

The aliases p1 and p2 represent two instances of the same table.

The query matches patients from the same county. The condition p1.patient_id < p2.patient_id prevents a patient from being matched with themselves and avoids returning the same pair twice.

For example, Amina Otieno and Isaac Kamau both come from Nairobi, while Esther Achieng and Brian Omondi both come from Kisumu.

When should you use SELF JOIN? Use it when comparing records within one table, such as patients from the same county or employees who share a manager.

Joining multiple hospital tables

Joins can combine several tables to create a more complete hospital visit report.

SELECT v.visit_id, v.visit_date, p.patient_name, d.doctor_name, dep.department_name, dg.diagnosis_name, pr.procedure_name, w.ward_name, v.total_bill_kes FROM morning.visit_flat AS v INNER JOIN morning.patients AS p ON v.patient_id = p.patient_id INNER JOIN morning.doctor AS d ON v.doctor_id = d.doctor_id INNER JOIN morning.department AS dep ON v.department_id = dep.department_id INNER JOIN morning.diagnosis AS dg ON v.diagnosis_id = dg.diagnosis_id INNER JOIN morning.procedure AS pr ON v.procedure_id = pr.procedure_id INNER JOIN morning.words AS w ON v.ward_id = w.ward_id ORDER BY v.visit_date, v.visit_id;

This query combines visit, patient, doctor, department, diagnosis, procedure, and ward information. It returns 30 rows for the supplied dataset.

Summary of join types

Join type Purpose
INNER JOIN Returns matching records from both tables.
LEFT JOIN Preserves all left-table records.
RIGHT JOIN Preserves all right-table records.
FULL OUTER JOIN Preserves records from both tables, matched where possible.
CROSS JOIN Generates every possible combination.
SELF JOIN Compares records within the same table.

2. SQL Window Functions: Analysing Data Without Losing Rows

Window functions perform calculations across a group of related rows while keeping each individual row in the result.

Unlike GROUP BY, which normally combines rows into summary records, window functions allow us to display individual visits alongside calculated values.

Understanding OVER()

The OVER() clause defines the window of rows used by a window function.

Example 

SELECT visit_id, total_bill_kes, AVG(total_bill_kes) OVER () AS overall_average_bill FROM morning.visit_flat ORDER BY visit_id;

The query calculates the average bill across all 30 visits and displays that same average beside every visit.

PARTITION BY

PARTITION BY divides the rows into groups for a window calculation.

Example 

SELECT visit_id, doctor_id, total_bill_kes, AVG(total_bill_kes) OVER ( PARTITION BY doctor_id ) AS doctor_average_bill FROM morning.visit_flat ORDER BY doctor_id, visit_id;

This calculates a separate average bill for each doctor while retaining all individual visit records.

ORDER BY inside a window

The ORDER BY inside OVER() defines the order used by the window calculation. It is different from the final query-level ORDER BY, which controls how the results are displayed.

Example 

SELECT visit_id, visit_date, total_bill_kes, SUM(total_bill_kes) OVER ( ORDER BY visit_date, visit_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM morning.visit_flat ORDER BY visit_date, visit_id;

The running total increases as each visit is processed in date and visit ID order.

Window frames

A window frame defines which rows are included in a calculation relative to the current row.

Common frame boundaries include:

  • UNBOUNDED PRECEDING: the beginning of the partition.
  • CURRENT ROW: the current row.
  • UNBOUNDED FOLLOWING: the end of the partition.

For example, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW calculates a running total from the first row up to the current row.

Explicitly specifying a frame is particularly important for functions such as LAST_VALUE(), where the default frame may not extend to the final row of the entire partition.

ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential number to each row within a window.

Example: Number visits by bill for each doctor 

SELECT visit_id, doctor_id, total_bill_kes, ROW_NUMBER() OVER ( PARTITION BY doctor_id ORDER BY total_bill_kes DESC, visit_id ) AS visit_number FROM morning.visit_flat ORDER BY doctor_id, visit_number;

Explanation

The query partitions visits by doctor and numbers them from the highest bill to the lowest bill.

For doctor 101, visit 1017, with a bill of KSh 82,000, receives row number 1. Visit 1010, with a bill of KSh 76,000, receives row number 2.

The visit_id acts as a tie-breaker when bills are equal.

When should you use ROW_NUMBER()? Use it when assigning sequential numbers, selecting the first record in a group, or identifying duplicate records.

RANK() and DENSE_RANK()

Both functions rank records according to an ordering, but they handle ties differently.

Example 

SELECT visit_id, total_bill_kes, RANK() OVER ( ORDER BY total_bill_kes DESC ) AS bill_rank, DENSE_RANK() OVER ( ORDER BY total_bill_kes DESC ) AS dense_bill_rank FROM morning.visit_flat ORDER BY bill_rank, visit_id;

Explanation

  • RANK() assigns equal ranks to tied bills and leaves gaps afterward.
  • DENSE_RANK() assigns equal ranks to tied bills without leaving gaps.

For example, two visits with bills of KSh 6,500 receive the same rank. With RANK(), the next rank is skipped; with DENSE_RANK(), the next distinct bill receives the next consecutive rank.

When should you use them? Use these functions when ranking visits, doctors, departments, or other records where tied values should share a position.

NTILE()

NTILE(n) divides an ordered result into approximately equal-sized groups.

Example: Divide visits into four billing groups 

SELECT visit_id, total_bill_kes, NTILE(4) OVER ( ORDER BY total_bill_kes DESC, visit_id ) AS bill_quartile FROM morning.visit_flat ORDER BY bill_quartile, total_bill_kes DESC;

Explanation

The 30 visits are divided into four groups based on their bills. Because 30 cannot be divided evenly into four whole-number groups, some groups contain seven rows, and others contain eight.

Group 1 contains the highest-billed visits, while group 4 contains the lowest-billed visits.

When should you use NTILE()? Use it to divide data into quartiles or other approximately equal-sized groups for descriptive analysis. Billing quartiles are financial groupings, not measures of clinical severity.

LAG()

LAG() retrieves a value from a preceding row without requiring a self join.

Example: Compare each patient's bill with their previous bill 

SELECT patient_id, visit_id, visit_date, total_bill_kes, LAG(total_bill_kes) OVER ( PARTITION BY patient_id ORDER BY visit_date, visit_id ) AS previous_bill FROM morning.visit_flat ORDER BY patient_id, visit_date;

Explanation

The query compares each visit with the previous recorded visit for the same patient.

Amina Otieno's first visit has no previous bill, so the value is NULL. Her second recorded visit has a bill of KSh 4,200, and the previous bill is KSh 3,500.

When should you use LAG()? Use it to compare current and previous transactions, calculate changes over time, or examine changes between consecutive visits.

LEAD()

LEAD() retrieves a value from a following row.

Example 

SELECT patient_id, visit_id, visit_date, total_bill_kes, LEAD(total_bill_kes) OVER ( PARTITION BY patient_id ORDER BY visit_date, visit_id ) AS next_bill FROM morning.visit_flat ORDER BY patient_id, visit_date;

The query displays the bill from each patient's next recorded visit. If a patient has no later visit, the result is NULL.

For Amina Otieno, the first visit's next bill is KSh 4,200.

When should you use LEAD()? Use it to examine subsequent transactions, visits, or events in an ordered dataset.

FIRST_VALUE()

FIRST_VALUE() returns the value from the first row of the window frame.

Example: Find each patient's first recorded bill 

SELECT patient_id, visit_id, visit_date, total_bill_kes, FIRST_VALUE(total_bill_kes) OVER ( PARTITION BY patient_id ORDER BY visit_date, visit_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS first_bill FROM morning.visit_flat ORDER BY patient_id, visit_date;

The query displays each patient's first recorded bill alongside every visit for that patient.

For Amina Otieno, the first bill is KSh 3,500.

When should you use FIRST_VALUE()? Use it to compare each record with the first value or event in a group.

LAST_VALUE()

LAST_VALUE() returns the value from the last row of the window frame.

Example: Find each patient's last recorded bill 

SELECT patient_id, visit_id, visit_date, total_bill_kes, LAST_VALUE(total_bill_kes) OVER ( PARTITION BY patient_id ORDER BY visit_date, visit_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS last_bill FROM morning.visit_flat ORDER BY patient_id, visit_date;

The explicit window frame ensures that the calculation reaches the final row of each patient's partition.

For Amina Otieno, the last recorded bill is KSh 4,200, which appears alongside both of her visits.

When should you use LAST_VALUE()? Use it to compare records with the final value in an ordered group.

SUM() OVER()

SUM() calculates a sum across a window without collapsing individual rows.

Example: Calculate cumulative hospital billing 

SELECT visit_id, visit_date, total_bill_kes, SUM(total_bill_kes) OVER ( ORDER BY visit_date, visit_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_billing FROM morning.visit_flat ORDER BY visit_date, visit_id;

The cumulative total increases with each visit. The final total is KSh 488,000, representing the sum of all 30 visit bills in the dataset.

When should you use SUM()? Use it for running totals and group-level financial calculations.

AVG() OVER()

AVG() calculates an average across a window.

Example: Compare bills with their department average 

SELECT visit_id, department_id, total_bill_kes, ROUND( AVG(total_bill_kes) OVER ( PARTITION BY department_id ), 2 ) AS department_average_bill FROM morning.visit_flat ORDER BY department_id, visit_id;

Each visit remains visible alongside the average bill for its department.

For department 10, the average bill is approximately KSh 23,344.44.

When should you use AVG()? Use it to compare individual records with their group's average without losing the original rows.

COUNT() OVER()

COUNT() can count rows in a window while retaining individual records.

Example: Count visits per patient 

SELECT patient_id, visit_id, COUNT(*) OVER ( PARTITION BY patient_id ) AS patient_visit_count FROM morning.visit_flat ORDER BY patient_id, visit_id;

Patients with two recorded visits receive a count of 2 on each of their visit rows. For example, patients with IDs 1 and 2 each have two visits.

When should you use COUNT()? Use it to identify repeat visits and measure how frequently records appear in a group.

MIN() and MAX() OVER()

MIN() returns the smallest value in a window, while MAX() returns the largest.

Example 

SELECT visit_id, department_id, total_bill_kes, MIN(total_bill_kes) OVER ( PARTITION BY department_id ) AS minimum_department_bill, MAX(total_bill_kes) OVER ( PARTITION BY department_id ) AS maximum_department_bill FROM morning.visit_flat ORDER BY department_id, visit_id;

For department 10, the minimum bill is KSh 3,500, and the maximum bill is KSh 82,000.

When should you use MIN() and MAX()? Use them to identify the smallest and largest values within a group while keeping each original record visible.

Summary of window functions

Function Purpose
ROW_NUMBER() Assigns a unique sequential number to each row.
RANK() Ranks rows and leaves gaps after ties.
DENSE_RANK() Ranks rows without gaps after ties.
NTILE() Divides ordered rows into approximately equal-sized groups.
LAG() Retrieves a value from a preceding row.
LEAD() Retrieves a value from a following row.
FIRST_VALUE() Returns the first value in the window frame.
LAST_VALUE() Returns the last value in the window frame.
SUM() Calculates a sum across the window.
AVG() Calculates an average across the window.
COUNT() Counts rows or non-null values in the window.
MIN() Returns the smallest value in the window.
MAX() Returns the largest value in the window.

3. Common Table Expressions (CTEs): Making Queries Easier to Read

A Common Table Expression is a named result set defined using the WITH clause. It exists for the duration of one SQL statement and helps organise complex queries into logical steps.

Basic CTE

Example: Find visits with bills above KSh 20,000 

WITH high_bill_visits AS ( SELECT visit_id, patient_id, visit_date, total_bill_kes FROM morning.visit_flat WHERE total_bill_kes > 20000 ) SELECT * FROM high_bill_visits ORDER BY total_bill_kes DESC;

Explanation

The CTE selects visits whose bills exceed KSh 20,000. The main query then retrieves the filtered records and sorts them by bill.

When should you use a basic CTE? Use it when you want to separate a filtering or calculation step from the main query to make the SQL easier to read.

Using multiple CTEs

CTEs can be chained together so that later CTEs use the results of earlier ones.

Example: Calculate total billing by department 

WITH department_billing AS ( SELECT department_id,Source：https://dev.to/victoria_ndei_07224d0b8db/-sql-essentials-joins-window-functions-ctes-and-subqueries-using-a-hospital-database-2n39
My favorites
PreviousServer-Rendered Price Tables: Lessons from Two Gold-Pricing Dashboards in MedanNextCloudflare Bought Deno: What It Means for Your Deployments

Related posts

Your Stripe inbox prevents duplicate updates. What about duplicate emails?

Your Stripe inbox prevents duplicate updates. What about duplicate emails?

dev.to C#
CloudNativePG on k3s: PostgreSQL That Survives Node Failures

CloudNativePG on k3s: PostgreSQL That Survives Node Failures

dev.to Kubernetes
The One-Story Resume Test: Building a Flagship Project Section That Still Passes ATS

The One-Story Resume Test: Building a Flagship Project Section That Still Passes ATS

dev.to Programming
UTM parametrelerini CRM'e doğru taşımak: İlk ve son temas, normalize sözlük, kanal eşleme tablosu ve geriye dönük yeniden hesaplama

UTM parametrelerini CRM'e doğru taşımak: İlk ve son temas, normalize sözlük, kanal eşleme tablosu ve geriye dönük yeniden hesaplama

dev.to JavaScript
I Cut 2,000 Flushes to Zero and fsync Still Returned Success

I Cut 2,000 Flushes to Zero and fsync Still Returned Success

dev.to Linux
Magnus Hagander: Nordic PGDay 2027 - Sponsorships for Nordic PGDay 2027 Are Now Open

Magnus Hagander: Nordic PGDay 2027 - Sponsorships for Nordic PGDay 2027 Are Now Open

Planet PostgreSQL