# SQL Essentials: Joins, Window Functions, CTEs, and Subqueries Using a Hospital Database

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,
        COUNT(*) AS visit_count,
        SUM(total_bill_kes) AS total_billing
    FROM morning.visit_flat
    GROUP BY department_id
),
department_report AS (
    SELECT
        d.department_name,
        db.visit_count,
        db.total_billing
    FROM department_billing AS db
    INNER JOIN morning.department AS d
        ON db.department_id = d.department_id
)
SELECT *
FROM department_report
ORDER BY total_billing DESC;

Explanation

The first CTE calculates the number of visits and total billing for each department. The second joins those results to the department table to display department names.

Expected result

Department	Visits	Total billing
Cardiology	9	KSh 210,100
Orthopedics	6	KSh 105,200
General Medicine	9	KSh 97,800
Pediatrics	6	KSh 74,900

When should you use multiple CTEs? Use them when a query has several logical stages, such as aggregation followed by joining and filtering.

Recursive CTE

A recursive CTE references itself to generate a sequence or traverse hierarchical data. It typically contains an initial query and a recursive query connected by UNION ALL.

The supplied hospital database does not contain a natural parent-child hierarchy, so this example generates a calendar sequence using the hospital visit dates.

Example: Generate dates between the first and last visit

WITH RECURSIVE visit_calendar AS (
    SELECT MIN(visit_date) AS calendar_date
    FROM morning.visit_flat

    UNION ALL

    SELECT calendar_date + 1
    FROM visit_calendar
    WHERE calendar_date < (
        SELECT MAX(visit_date)
        FROM morning.visit_flat
    )
)
SELECT calendar_date
FROM visit_calendar
ORDER BY calendar_date;

Explanation

The initial query selects the earliest visit date. The recursive query adds one day to the previous date until it reaches the latest visit date.

The result contains 15 dates, from 2026-06-01 to 2026-06-15.

When should you use a recursive CTE? Use it for generating sequences, traversing hierarchies, or processing data that requires repeated steps.

CTEs with joins and aggregations

CTEs can combine aggregation and joins to produce readable summary reports.

Example: Department billing and average satisfaction

WITH department_metrics AS (
    SELECT
        department_id,
        COUNT(*) AS visit_count,
        SUM(total_bill_kes) AS total_billing,
        ROUND(AVG(patient_satisfaction_score), 2)
            AS average_satisfaction
    FROM morning.visit_flat
    GROUP BY department_id
)
SELECT
    d.department_name,
    dm.visit_count,
    dm.total_billing,
    dm.average_satisfaction
FROM department_metrics AS dm
INNER JOIN morningdepartment AS d
    ON dm.department_id = d.department_id
ORDER BY dm.total_billing DESC;

Explanation

The CTE calculates visit counts, total billing, and average satisfaction scores. The main query adds department names by joining the aggregated result to department.

This produces a report that combines operational and financial metrics.

CTEs versus subqueries

Feature	CTE	Subquery
Definition	Uses WITH.	Nested inside another query.
Readability	Often clearer for multi-step logic.	Often concise for simple calculations.
Reuse	Can be referenced multiple times in one statement.	May need to be repeated if the same logic is reused.
Recursive processing	Supports recursive queries using WITH RECURSIVE.	Not the usual approach for recursive processing.
Performance	Depends on the query and execution plan.	Also depends on the query and execution plan.

Neither approach is automatically faster. Choose based on readability, maintainability, and the execution plan.

---

4. Subqueries: Queries Inside Other Queries

A subquery is a query nested inside another SQL statement. It can return a single value, multiple rows, or a table-like result.

Subqueries can appear in SELECT, FROM, and WHERE clauses.

Scalar subquery

A scalar subquery returns a single value: one row and one column.

Example: Display the average bill alongside each visit

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

Explanation

The inner query calculates the average bill across all visits. The outer query displays that value alongside every visit.

When should you use a scalar subquery? ** Use it when a single calculated value is needed within a larger query.

Non-correlated subquery

A non-correlated subquery does not depend on values from the outer query.

Example: Find visits with bills above the overall average

SELECT
    visit_id,
    patient_id,
    total_bill_kes
FROM morning.visit_flat
WHERE total_bill_kes > (
    SELECT AVG(total_bill_kes)
    FROM morning.visit_flat
)
ORDER BY total_bill_kes DESC;

Explanation

The inner query calculates the overall average bill. The outer query returns visits whose bills exceed that value.

The subquery does not reference the outer query, so it is non-correlated.

When should you use it? ** Use it when comparing records against a general benchmark, such as an overall average.

Correlated subquery

A correlated subquery refers to a value from the outer query. Its result depends on the current outer row.

Example: Find visits above the average bill for that patient

SELECT
    v.visit_id,
    v.patient_id,
    v.total_bill_kes
FROM morning.visit_flat AS v
WHERE v.total_bill_kes > (
    SELECT AVG(v2.total_bill_kes)
    FROM morning.visit_flat AS v2
    WHERE v2.patient_id = v.patient_id
)
ORDER BY v.patient_id, v.visit_id;

Explanation

For each visit, the inner query calculates the average bill for the same patient. The outer query returns visits whose bills exceed that patient's average.

Patients with only one visit will not appear because their bill equals their own average.

When should you use it? Use it for comparisons within a group, such as comparing each visit with its patient's average.

Nested subquery

A nested subquery contains another subquery inside it.

Example: Find visits above the average bill for chronic-disease diagnoses

SELECT
    visit_id,
    diagnosis_id,
    total_bill_kes
FROM morning.visit_flat
WHERE total_bill_kes > (
    SELECT AVG(total_bill_kes)
    FROM morning.visit_flat
    WHERE diagnosis_id IN (
        SELECT diagnosis_id
        FROM morning.diagnosis
        WHERE diagnosis_category = 'Chronic Disease'
    )
)
ORDER BY total_bill_kes DESC;

Explanation

1. The innermost query identifies diagnoses in the chronic-disease category.
2. The middle query calculates the average bill for visits with those diagnoses.
3. The outer query returns visits whose bills exceed that average.

The outer query is not restricted to chronic-disease visits; it compares all visits with the calculated benchmark.

When should you use it? Use nested subqueries when one query must identify a set of records before another query can calculate or filter results.

Subqueries using IN

The IN operator checks whether a value matches any value returned by a list or subquery.

Example: Find visits associated with chronic diseases

SELECT
    visit_id,
    diagnosis_id,
    total_bill_kes
FROM morning.visit_flat
WHERE diagnosis_id IN (
    SELECT diagnosis_id
    FROM morning.diagnosis
    WHERE diagnosis_category = 'Chronic Disease'
)
ORDER BY visit_id;

The inner query returns diagnosis IDs in the chronic-disease category. The outer query returns visits associated with those diagnoses.

When should you use IN? Use it when filtering records based on membership in a set of values.

Subqueries using EXISTS

EXISTS checks whether a subquery returns at least one row.

Example: Find patients with multiple visits

SELECT
    p.patient_id,
    p.patient_name
FROM morning.patients AS p
WHERE EXISTS (
    SELECT 1
    FROM morning.visit_flat AS v
    WHERE v.patient_id = p.patient_id
    GROUP BY v.patient_id
    HAVING COUNT(*) > 1
)
ORDER BY p.patient_id;

Explanation

For each patient, the subquery checks whether that patient has more than one visit. EXISTS returns true when the subquery finds a matching group.

Patients with multiple visits include Amina Otieno, John Kariuki, Esther Achieng, Lucy Wambui, and Peter Ouma.

When should you use EXISTS? Use it when the goal is to check whether related records exist rather than retrieve those records.

Subqueries using ANY

ANY compares a value with the results of a subquery. The condition is true if it is satisfied for at least one returned value.

Example: Find visits with bills greater than at least one inpatient bill

SELECT
    visit_id,
    admission_type,
    total_bill_kes
FROM morning.visit_flat
WHERE total_bill_kes > ANY (
    SELECT total_bill_kes
    FROM morning.visit_flat
    WHERE admission_type = 'Inpatient'
)
ORDER BY total_bill_kes DESC;

Explanation

The subquery returns the bills of inpatient visits. A visit satisfies the condition if its bill is greater than at least one of those bills.

For a non-empty set of values, > ANY is equivalent to being greater than the smallest value in that set.

When should you use ANY? Use it when a comparison needs to be satisfied by at least one value in a result set.

Subqueries using ALL

ALL compares a value with every value returned by a subquery. The condition must be true for all returned values.

Example: Find visits whose bills are at least as high as every inpatient bill

SELECT
    visit_id,
    admission_type,
    total_bill_kes
FROM morning.visit_flat
WHERE total_bill_kes >= ALL (
    SELECT total_bill_kes
    FROM morning.visit_flat
    WHERE admission_type = 'Inpatient'
)
ORDER BY total_bill_kes DESC;

Explanation

The condition requires a visit's bill to be greater than or equal to every inpatient bill.

The highest inpatient bill in the dataset is KSh 82,000, recorded for visit 1017. Therefore, that visit satisfies the condition.

If we used > ALL instead, no rows would be returned because no bill exceeds KSh 82,000.

When should you use ALL? Use it when a value must satisfy a comparison against every value in a set.

Subqueries in the SELECT clause

A subquery in the SELECT clause can add a calculated value to each output row.

Example: Display the highest bill alongside each visit

SELECT
    visit_id,
    total_bill_kes,
    (
        SELECT MAX(total_bill_kes)
        FROM morning.visit_flat
    ) AS highest_bill_in_dataset
FROM morning.visit_flat
ORDER BY visit_id;

The highest_bill_in_dataset column displays KSh 82,000 on every row, while the individual visit bills vary.

When should you use it? ** Use a scalar subquery in SELECT when each row needs a shared benchmark or summary value.

Subqueries in the FROM clause

A subquery in the FROM clause acts as a derived table. In PostgreSQL, it must have an alias.

Example: Count visits for each patient

SELECT
    p.patient_name,
    patient_summary.visit_count
FROM (
    SELECT
        patient_id,
        COUNT(*) AS visit_count
    FROM morning.visit_flat
    GROUP BY patient_id
) AS patient_summary
INNER JOIN morning.patients AS p
    ON patient_summary.patient_id = p.patient_id
ORDER BY patient_summaryvisit_count DESC, p.patient_name;

Explanation

The derived table calculates the number of visits per patient. The outer query joins those counts to patients to display patient names.

When should you use it? ** Use a derived table when an aggregated or transformed result needs to be used as input for a larger query.

Summary of subquery types

Type	Purpose
Scalar	Returns one value.
Non-correlated	Does not depend on the outer query.
Correlated	References values from the outer query.
Nested	Contains another subquery.
IN	Checks membership in a set of values.
EXISTS	Checks whether a matching row exists.
ANY	Requires a comparison to be true for at least one returned value.
ALL	Requires a comparison to be true for every returned value.
In SELECT	Adds a calculated value to the output.
In FROM	Creates a derived table.
In WHERE	Filters records using another query's result.

---

5. CTEs vs Subqueries

Both CTEs and subqueries help structure query logic, but they differ in how the logic is expressed.

Feature	CTE	Subquery
Definition	Uses WITH.	Nested inside another query.
Readability	Often clearer for multi-step logic.	Often concise for simple logic.
Reuse	Can be referenced multiple times within one statement.	May need to be repeated if the same logic is reused.
Recursive queries	Supported using WITH RECURSIVE.	Not the usual approach for recursive processing.
Performance	Depends on the query plan.	Also depends on the query plan.

Neither approach is universally faster. The choice should depend on clarity, maintainability, and the execution plan produced by PostgreSQL.

6. Combining These Concepts

The real value of SQL comes from combining these techniques to answer practical questions.

For example, we can rank visits by bill within each department, then join the result to the department table to display department names.

WITH RankedVisits AS (
    SELECT
        department_id,
        visit_id,
        patient_id,
        total_bill_kes,
        ROW_NUMBER() OVER (
            PARTITION BY department_id
            ORDER BY total_bill_kes DESC, visit_id
        ) AS rn
    FROM morning.visit_flat
)
SELECT
    dep.department_name,
    r.visit_id,
    p.patient_name,
    r.total_bill_kes
FROM RankedVisits AS r
INNER JOIN morning.department AS dep
    ON r.department_id = dep.department_id
INNER JOIN morning.patients AS p
    ON r.patient_id = p.patient_id
WHERE r.rn = 1
ORDER BY r.total_bill_kes DESC;

This query combines three concepts:

1. CTE: Organises the visit-ranking logic.
2. Window function: Ranks visits within each department.
3. Joins: Retrieves department names and patient names.

The result contains one highest-billed visit for each department. The visit_id tie-breaker makes the ranking consistent when bills are equal.

Conclusion

Joins, window functions, CTEs, and subqueries are essential tools for writing effective SQL queries.

Joins combine related data from multiple tables. Window functions analyse and compare rows without losing individual records. CTEs organise complex queries into readable steps, while recursive CTEs support sequences and hierarchical processing. Subqueries allow one query to use the results of another for filtering, calculations, and comparisons.

Working with the hospital dataset demonstrates how these concepts can answer practical questions about patient visits, doctor activity, diagnoses, department billing, and repeat visits.

Understanding the problem before choosing a SQL technique is essential. With practice, these concepts can help you write queries that are clearer and more maintainable.