A Practical Guide to SQL Joins, Subqueries, CTEs, and Window Functions.

A hospital database rarely stores every useful detail in one table. Patient details may be stored separately from visit records. Doctor, department, ward, diagnosis, and procedure information may live in their own tables.

That structure is useful because it reduces duplication, but it also means that answering practical questions requires more than a basic SELECT statement.

For example:

• Which doctors handled the most visits in each department?
• Which patients have had emergency visits?
• Which visits had bills above the hospital average?
• How has each patient’s visit history changed over time?

This article answers those questions using four SQL techniques: joins, subqueries, common table expressions, and window functions.

Before starting any query, we must identify the grain. For this article, we use a hospital visits table, supported by tables for patients, doctors, departments, wards, diagnoses, and medical procedure information.

Our grain for the visits table is one row per visit.

SQL joins: combining hospital records

A join combines rows from two or more tables using a related column. In this database, hospital.fact_visits contains foreign keys that point to the supporting tables.

Why we join tables.

• Recombine data. This is when you want to see all the data in one place.
• Data Enrichment. When we want to get extra information.
• Check Existence. Very useful when you want to perform filtering functions

INNER JOIN: return only matching records

An INNER JOIN returns rows that have a match in both tables. The order of the tables does not matter.

Question: Which visits can be matched to a patient?

SELECT
    fv.visit_id,
    fv.visit_date,
    p.patient_name
FROM hospital.fact_visits AS fv
INNER JOIN hospital.patients AS p
    ON p.patient_id = fv.patient_id;

The query connects each visit to its patient name. The result is one row per visit with a matching patient record. If a visit had no matching patient, it would not appear.

LEFT JOIN: keep every row from the left table

A left join returns every row from the left table and only matching rows from the right table. Order of the tables matters. If no matching row exists on the right, the right-side columns become NULL.

Question: Which patients have no recorded visit?

SELECT
    p.patient_id,
    p.patient_name
FROM hospital.patients AS p
LEFT JOIN hospital.fact_visits AS fv
    ON fv.patient_id = p.patient_id
WHERE fv.visit_id IS NULL;

The patients table is on the left, so every patient is considered. The WHERE clause keeps only patients for whom no visit was found.

RIGHT JOIN: keep every row from the right table

A right join returns all rows from the right and only matching rows from the left.

What happens when every department must remain visible?

SELECT
    dep.department_name,
    fv.visit_id
FROM hospital.fact_visits AS fv
RIGHT JOIN hospital.departments AS dep
    ON dep.department_id = fv.department_id;

Every department appears, including a department with no matching visit.

FULL OUTER JOIN: preserve both sources

A full outer join returns all rows from both tables. It is useful when comparing two summaries.

Which departments appear in either the outpatient or emergency summary?

WITH outpatient AS (
    SELECT department_id, COUNT(*) AS outpatient_visits
    FROM hospital.fact_visits
    WHERE admission_type = 'Outpatient'
    GROUP BY department_id
), emergency AS (
    SELECT department_id, COUNT(*) AS emergency_visits
    FROM hospital.fact_visits
    WHERE admission_type = 'Emergency'
    GROUP BY department_id
)
SELECT
    COALESCE(o.department_id, e.department_id) AS department_id,
    o.outpatient_visits,
    e.emergency_visits
FROM outpatient AS o
FULL OUTER JOIN emergency AS e
    ON e.department_id = o.department_id;

A department found only in one summary is still retained. The missing summary value is NULL. The query uses two small department summaries instead of joining every hospital table.

CROSS JOIN: create every combination

A cross join pairs every row from one source with every row from another source.

What department-and-shift combinations could a staffing plan contain?

WITH shifts(shift_name) AS (
    VALUES ('Morning'), ('Evening'), ('Night')
)
SELECT
    dep.department_name,
    shifts.shift_name
FROM hospital.departments AS dep
CROSS JOIN shifts;

The database has four departments, and the example has three shifts, so the expected result contains 12 combinations. A cross join is useful for planning grids, but an accidental cross join can create far too many rows.

Self join: compare rows in the same source

A self-join uses the same table twice with different aliases.

Which triage level is immediately more severe than another level?

WITH triage_levels(level_name, severity) AS (
    VALUES
        ('Green', 1),
        ('Yellow', 2),
        ('Orange', 3),
        ('Red', 4)
)
SELECT
    current_level.level_name,
    next_level.level_name AS next_more_severe_level
FROM triage_levels AS current_level
JOIN triage_levels AS next_level
    ON next_level.severity = current_level.severity + 1;

Subqueries: using one query inside another

A subquery is a query inside a larger query. It can appear in SELECT, FROM, or WHERE.

Scalar and non-correlated subqueries

A scalar subquery returns one value. A non-correlated subquery can run independently of the outer query.

Which visits had bills above the overall average?

SELECT
    visit_id,
    patient_id,
    total_bill_kes
FROM hospital.fact_visits
WHERE total_bill_kes > (
    SELECT AVG(total_bill_kes)
    FROM hospital.fact_visits
);

The inner query returns the average bill. The outer query returns visits above that value.

Correlated subquery in SELECT

A correlated subquery refers to a column from the outer query.

What is the recorded visit count for each patient?

SELECT
    p.patient_id,
    p.patient_name,
    (
        SELECT COUNT(*)
        FROM hospital.fact_visits AS fv
        WHERE fv.patient_id = p.patient_id
    ) AS visit_count
FROM hospital.patients AS p;

For each patient, the inner query counts matching visits. The reference to p.patient_id makes it correlated.

Subquery in FROM

A subquery in the FROM clause creates a temporary derived table for the outer query.

Which departments recorded at least five visits?

SELECT
    department_summary.department_id,
    department_summary.visit_count
FROM (
    SELECT
        department_id,
        COUNT(*) AS visit_count
    FROM hospital.fact_visits
    GROUP BY department_id
) AS department_summary
WHERE visit_count >= 5;

The inner query creates one row per department. The outer query filters those rows. The expected result is one row per department meeting the threshold.

IN: Checks if a value matches any value in a list

Which patients have had an emergency visit?

SELECT
    p.patient_id,
    p.patient_name
FROM hospital.patients AS p
WHERE p.patient_id IN (
    SELECT fv.patient_id
    FROM hospital.fact_visits AS fv
    WHERE fv.admission_type = 'Emergency'
);

The subquery returns patient IDs from emergency visits. IN keeps patients whose IDs appear in that list.

EXISTS: check whether a row exists

The same question can be answered with EXISTS:

SELECT
    p.patient_id,
    p.patient_name
FROM hospital.patients AS p
WHERE EXISTS (
    SELECT 1
    FROM hospital.fact_visits AS fv
    WHERE fv.patient_id = p.patient_id
      AND fv.admission_type = 'Emergency'
);

EXISTS is true when the inner query returns at least one row. It is useful when we care about whether a relationship exists, not how many matching rows there are.

ANY, ALL, and nested subqueries

Which visits had a bill higher than at least one emergency visit?

SELECT
    visit_id,
    total_bill_kes
FROM hospital.fact_visits
WHERE total_bill_kes > ANY (
    SELECT emergency.total_bill_kes
    FROM hospital.fact_visits AS emergency
    WHERE emergency.admission_type = 'Emergency'
);

ANY returns true if a value matches any value in any value in a list.
ALL returns true if a value matches all values in a list.

CTEs: naming complicated steps

A common table expression, or CTE, is a named query introduced with WITH. It is useful when a query has several logical stages.

Basic CTE

Which departments recorded at least five visits?

WITH department_visit_counts AS (
    SELECT
        department_id,
        COUNT(*) AS visit_count
    FROM hospital.fact_visits
    GROUP BY department_id
)
SELECT
    department_id,
    visit_count
FROM department_visit_counts
WHERE visit_count >= 5;

The CTE creates the department summary. The main query filters it. This is the same task as the derived-table example, but the intermediate step has a clearer name.

PostgreSQL describes CTEs as a way to break complicated queries into simpler auxiliary statements.

Multiple CTEs with a join

Which doctors handled the most visits and what specialties do they have?

WITH doctor_visit_counts AS (
    SELECT
        doctor_id,
        COUNT(*) AS visit_count
    FROM hospital.fact_visits
    GROUP BY doctor_id
)
SELECT
    d.doctor_name,
    d.doctor_specialty,
    dvc.visit_count
FROM hospital.doctors AS d
JOIN doctor_visit_counts AS dvc
    ON dvc.doctor_id = d.doctor_id
ORDER BY dvc.visit_count DESC;

The CTE counts visits. The final query joins that small result to the doctor table. The expected result is one row per doctor represented in the visit data.

Recursive CTE: generate reporting dates

The schema has no employee or department hierarchy, so a recursive date example is more honest than inventing one.

Can we generate one reporting date for every day in the supplied date range?

WITH RECURSIVE reporting_dates AS (
    SELECT DATE '2026-06-01' AS report_date

    UNION ALL

    SELECT (report_date + INTERVAL '1 day')::date
    FROM reporting_dates
    WHERE report_date < DATE '2026-06-15'
)
SELECT report_date
FROM reporting_dates
ORDER BY report_date;

The first query is the starting row. The recursive query adds one day until June 15. The expected result is 15 dates.

A CTE is not automatically faster than a subquery. Use a CTE when naming the steps makes the query easier to understand; then inspect an execution plan when performance matters.

Window functions: calculate without collapsing rows

Window functions calculate across related rows while preserving the individual rows. This is the main difference between a window function and GROUP BY.

A grouped query returns one row per patient:

SELECT
    patient_id,
    SUM(total_bill_kes) AS total_billed_kes
FROM hospital.fact_visits
GROUP BY patient_id;

A window query keeps one row per visit:

SELECT
    patient_id,
    visit_id,
    total_bill_kes,
    SUM(total_bill_kes) OVER (
        PARTITION BY patient_id
    ) AS total_billed_kes
FROM hospital.fact_visits;

The total appears beside every visit belonging to that patient.

The parts of a window

function_name(value) OVER (
    PARTITION BY grouping_columns
    ORDER BY ordering_columns
    ROWS BETWEEN ... AND ...
)

• OVER() Tells SQL that the function is a window calculation.
• PARTITION BY divides the result set into partitions.
• Window ORDER BY sorts the data within a window either in ascending or descending order
• A frame defines which rows around the current row are included. The frame clause can only be used together with the ORDER BY clause. Also, the lower value must be before the higher value

Ranking: ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE()

Which doctors rank highest within each department?

WITH doctor_counts AS (
    SELECT
        department_id,
        doctor_id,
        COUNT(*) AS visit_count
    FROM hospital.fact_visits
    GROUP BY department_id, doctor_id
)
SELECT
    dc.department_id,
    d.doctor_name,
    dc.visit_count,
    ROW_NUMBER() OVER (
        PARTITION BY dc.department_id
        ORDER BY dc.visit_count DESC, dc.doctor_id
    ) AS row_number_position,
    RANK() OVER (
        PARTITION BY dc.department_id
        ORDER BY dc.visit_count DESC
    ) AS rank_position,
    DENSE_RANK() OVER (
        PARTITION BY dc.department_id
        ORDER BY dc.visit_count DESC
    ) AS dense_rank_position,
    NTILE(4) OVER (
        PARTITION BY dc.department_id
        ORDER BY dc.visit_count DESC, dc.doctor_id
    ) AS workload_quartile
FROM doctor_counts AS dc
JOIN hospital.doctors AS d
    ON d.doctor_id = dc.doctor_id;

The CTE reduces the data to one row per doctor and department. The window functions then rank those rows.

• ROW_NUMBER() gives every row a unique number.
• RANK() gives tied rows the same rank and leaves gaps.
• DENSE_RANK() gives tied rows the same rank without gaps.
• NTILE(n) Divides the rows into a specified number of approximately equal groups.

If counts are 10, 8, 8, 5, RANK() produces 1, 2, 2, 4, while DENSE_RANK() produces 1, 2, 2, 3.

LAG() and LEAD() for patient history

What was a patient’s previous or next recorded visit?

SELECT
    patient_id,
    visit_id,
    visit_date,
    LAG(visit_date) OVER (
        PARTITION BY patient_id
        ORDER BY visit_date, visit_id
    ) AS previous_visit_date,
    LEAD(visit_date) OVER (
        PARTITION BY patient_id
        ORDER BY visit_date, visit_id
    ) AS next_visit_date
FROM hospital.fact_visits;

LAG() looks backward and LEAD() looks forward within each patient’s history. The first row has no previous date, and the last row has no next date, so NULL is expected at those boundaries.

FIRST_VALUE() and LAST_VALUE()

When did each patient first and most recently visit the hospital?

SELECT
    patient_id,
    visit_id,
    visit_date,
    FIRST_VALUE(visit_date) OVER (
        PARTITION BY patient_id
        ORDER BY visit_date, visit_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_visit_date,
    LAST_VALUE(visit_date) OVER (
        PARTITION BY patient_id
        ORDER BY visit_date, visit_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_visit_date
FROM hospital.fact_visits;

FIRST_VALUE() returns the first value in the frame, while LAST_VALUE() returns the last. The explicit full frame is important because the default frame can make LAST_VALUE() return the current row’s value instead of the patient’s final visit date.

Window aggregates: SUM(), AVG(), COUNT(), MIN(), and MAX()

How does each visit compare with the patient’s billing history?

SELECT
    patient_id,
    visit_id,
    visit_date,
    total_bill_kes,
    SUM(total_bill_kes) OVER (
        PARTITION BY patient_id
        ORDER BY visit_date, visit_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_bill_kes,
    AVG(total_bill_kes) OVER (
        PARTITION BY patient_id
    ) AS average_bill_kes,
    COUNT(*) OVER (
        PARTITION BY patient_id
    ) AS visit_count,
    MIN(total_bill_kes) OVER (
        PARTITION BY patient_id
    ) AS minimum_bill_kes,
    MAX(total_bill_kes) OVER (
        PARTITION BY patient_id
    ) AS maximum_bill_kes
FROM hospital.fact_visits;

The running SUM() uses an ordered frame from the patient’s first visit through the current visit. The other functions summarize the full patient partition.

The result remains one row per visit. visit_count counts visits, not distinct patients. total_bill_kes represents recorded billed amounts, not necessarily collected revenue.

Joins assemble the facts. Subqueries express focused conditions. CTEs organize multi-step reasoning. Window functions add context without hiding the individual visit records.

Together, these techniques turn a collection of hospital tables into answers to practical analytical questions.