JOINS, WINDOW FUNCTIONS, CTEs AND SUBQUERIES

JOINS

Joins are operations used to combine rows from two or more tables based on a related column between them.

Types of SQL Joins

1.INNER JOIN

It returns only the rows that have matching values in both tables. Applicable when you only need matching records.

Example

To check the number of visits by each patient:

select
    p.patient_id,
    p.patient_name,
    count(v.visit_id) as visits_number
from patients as p
inner join visits as v
    ON p.patient_id = v.patient_id
group by p.patient_id, p.patient_name
order by p.patient_id asc;

2. LEFT JOIN

Returns all rows from the left table, plus matching rows from the right table. If no match exists, the right-side columns contain NULL. Applicable when you must retain every record from your main table.

Example

Comparing hospital activity across doctors.
How many visits has each doctor handled, and what is their average patient satisfaction score?

select
    d.doctor_id,
    d.doctor_name,
    count(v.visit_id) as total_visits,
    round(avg(v.patient_satisfaction_score), 2)
        as avg_satisfaction
from doctors as d
left join visits as v
    on d.doctor_id = v.doctor_id
group by d.doctor_id, d.doctor_name
order by avg_satisfaction desc;

3.RIGHT JOIN

Keeps all records from the table on the right, even when a matching record on the left doesn't exist.

Example

Analyzing visits alongside doctor details

select
    v.visit_id,
    v.visit_date,
    v.patient_id,
    d.doctor_id,
    d.doctor_name
from doctors as d
right join visits as v
    on d.doctor_id = v.doctor_id
order by v.visit_date;

4.FULL OUTER JOIN

Returns all rows from both the left table (table1) and the right table (table2). When there is a match between the tables based on the join condition, the result set includes the matched rows from both tables.
When there is no match, the result set includes NULL values for columns from the table without a match. Applicable when comparing two datasets completely.

Example

Reconciling patient and visit records. Which records match between the patient registry and visit records, and which records appear on only one side?

select
    p.patient_id as registered_patient_id,
    p.patient_name,
    v.visit_id,
    v.patient_id as visit_patient_id
from patients as p
full outer join visits as v
    on p.patient_id = v.patient_id;

5.CROSS JOIN

Combines every row from one table with every row from another table. It produces a Cartesian product, meaning each row of the first table is paired with every row of the second table. It doesn't need a matching column or an ON condition.

Example

Generating every possible doctor–department combination

select
    d.doctor_name,
    dep.department_name
from doctors as d
cross join departments as dep
order by d.doctor_name, dep.department_name;

The cross join is especially useful for generating combinations and creating test data, but it should be used with caution due to the potential size of the result set.

6.SELF JOIN

Joins a table to itself. This is useful when you want to compare rows within the same table. It allows you to create relationships between rows in the same table based on certain conditions.

Example

Comparing hospital visits for the same patient
Which patients have returned to the hospital for another visit, and when did their visits occur?

select
    v1.patient_id,
    v1.visit_id as first_visit_id,
    v1.visit_date as first_visit_date,
    v2.visit_id as subsequent_visit_id,
    v2.visit_date as subsequent_visit_date
from visits as v1
inner join visits as v2
    on v1.patient_id = v2.patient_id
   and v1.visit_date < v2.visit_date
order by v1.patient_id, v1.visit_date;

WINDOW FUNCTIONS

Window functions perform calculations across a set of related rows while keeping every original row in the result. Unlike GROUP BY, which collapses rows into a summary, window functions let you add rankings, running totals, averages, and comparisons directly to detailed records.
The "OVER" clause defines the “window” of rows for the calculation. It can:

• PARTITION BY: It divides the data into groups using PARTITION BY.
• ORDER BY: It specifies the order of rows within each group using ORDER BY.

Types of Window Functions in SQL

Aggregate Window Functions

Aggregate window functions calculate aggregates over a window of rows while retaining individual rows.

1.COUNT()

Counts the rows within a window.

Example

Show every hospital visit while displaying how many visits each patient has made.

select
    patient_id,
    visit_id,
    visit_date,
    count(*) over (
        partition by patient_id
    ) as total_visits
from visits
order by patient_id, visit_date;

2.AVG()

Calculates an average over a window.

Example

select
    visit_id,
    doctor_id,
    patient_satisfaction_score,
    avg(patient_satisfaction_score) over (
        partition by doctor_id
    ) as doctor_avg_score
from visits
order by doctor_id, visit_id;

3.MIN()

Finds the minimum value.

Example

Display the lowest bill recorded for each doctor alongside every visit.

select
    visit_id,
    doctor_id,
    total_bill,
    min(total_bill) over (
        partition by doctor_id
    ) as lowest_doctor_bill
from visits
order by doctor_id, visit_id;

4.MAX()

Finds the maximum value.

5.SUM()

Used for cumulative calculations.

Example

Track how much each patient has spent over time.

select
    patient_id,
    visit_id,
    visit_date,
    total_bill,
    sum(total_bill) over (
        partition by patient_id
        order by visit_date
        rows between unbounded preceding
        and current row
    ) as cumulative_bill
from visits
order by patient_id, visit_date;

ROW_NUMBER()

Assigns a unique sequential number to each row within a partition.

Example

Number each patient's visits in chronological order.

select
    patient_id,
    visit_id,
    visit_date,
    row_number() over (
        partition by patient_id
        order by visit_date
    ) as visit_number
from visits
order by patient_id, visit_date;

RANK()

Ranks rows while giving equal values the same rank. Tied scores receive the same rank, and the next rank may be skipped.
Example
Rank hospital visits by patient satisfaction score.

select
    doctor_id,
    avg(patient_satisfaction_score) as avg_score,
    rank() over (
        order by avg(patient_satisfaction_score) desc
    ) as doctor_rank
from visits
group by doctor_id;

DENSE_RANK()

Assigns ranks to rows without skipping rank numbers for duplicates.

LAG()

Looks at the previous row.

Example

Compare a patient's current bill with their previous bill.

select
    patient_id,
    visit_date,
    total_bill,
    lag(total_bill) over (
        partition by patient_id
        order by visit_date
    ) as previous_bill
from visits;

LEAD()

Looks at the next row.

Example

Useful for predicting follow-up appointments.

select
    patient_id,
    visit_date,
    lead(visit_date) over (
        partition by patient_id
        order by visit_date
    ) as next_visit
from visits;

NTILE()

Divides ordered rows into n approximately equal groups, assigning each row a group number.

Example

Divide hospital visits into four groups based on their total bill.

select
    visit_id,
    total_bill,
    ntile(4) over (
        order by total_bill desc
    ) as bill_group
from visits;

FIRST_VALUE()

Gets the first value in a window.

Example

Suppose you want to display each patient's first recorded visit date alongside every visit.

select
    patient_id,
    visit_id,
    visit_date,
    first_value(visit_date) over (
        partition by patient_id
        order by visit_date, visit_id
    ) as first_visit_date
from visits
order by patient_id, visit_date, visit_id;

LAST_VALUE()

Gets the last value in a window.

Example

To retrieve each patient's last recorded visit date for every visit, specify the full window frame.

select
    patient_id,
    visit_id,
    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 visits
order by patient_id, visit_date, visit_id;

GROUP BY vs Window Functions

Group By	Window Functions
Combines rows into one summary row	Keeps every original row
One row per patient	One row per visit

COMMON TABLE EXPRESSIONS(CTES)

A CTE is a temporary result set in Sql that you can reference within a single query. A CTE exists only for the SQL statement in which it is defined. It doesn't create a permanent table in your database.
Used for

• Performing recursive operations, such as traversing hierarchical data.
• Breaking down multi-step calculations into manageable parts.
• Replacing nested subqueries in complex data retrieval tasks. ####CTE Syntax

with cte_name (column1, column2, ...) as (
    -- query that defines the cte
    select ...
    from ...
    where ...
)
-- main query
select ...
from cte_name;

Types of CTEs

1. Basic CTEs

For a simple query like this, the CTE is optional.

Example

Which visits had a total bill greater than 10,000?

with expensive_visits as (
    select
        visit_id,
        patient_id,
        total_bill
    from visits
    where total_bill > 10000
)
select *
from expensive_visits;

2.Multiple CTEs

You can define multiple CTEs in a single query, which allows for complex transformations and calculations.

Example

Suppose you want to identify doctors who have handled more than 20 visits and calculate their average patient satisfaction score.

with doctor_visits as (
    select
        doctor_id,
        count(*) as total_visits
    from visits
    group by doctor_id
),
doctor_satisfaction as (
    select
        doctor_id,
        avg(patient_satisfaction_score) as avg_score
    from visits
    group by doctor_id
)
select
    dv.doctor_id,
    dv.total_visits,
    round(ds.avg_score::numeric, 2) as avg_score
from doctor_visits as dv
inner join doctor_satisfaction as ds
    on dv.doctor_id = ds.doctor_id
where dv.total_visits > 20
order by avg_score desc;

Recursive CTEs

A recursive CTE references itself to retrieve hierarchical data.
Use MAXRECURSION to prevent infinite loops.
It consist of two parts:

• Anchor member: The initial query that selects the base case. It establishes the starting row or rows.
• Recursive member: The query that references the CTE itself, pulling the next level of data.

Example

Generate a daily hospital visit report. Suppose you want to report the number of visits for every calendar date between the earliest and latest dates in your visits table: including dates with zero visits.

with recursive date_series as (
    select min(visit_date) as visit_date
    from visits

    union all

    select visit_date + 1
    from date_series
    where visit_date < (
        select max(visit_date)
        from visits
    )
),
daily_visits as (
    select
        visit_date,
        count(*) as total_visits
    from visits
    group by visit_date
)
select
    ds.visit_date,
    coalesce(dv.total_visits, 0) as total_visits
from date_series as ds
left join daily_visits as dv
    on ds.visit_date = dv.visit_date
order by ds.visit_date;

Uses of CTEs

CTEs (Common Table Expressions) help simplify and organize complex SQL queries.

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

Limitations

While useful, CTEs come with a few practical constraints.

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

SQL SUBQUERY

Is a query nested inside another SQL query. The inner query produces a result that the outer query uses to filter, compare, or calculate something.
They are an essential tool when we need to perform operations like:

• Apply aggregate functions like SUM, COUNT or AVG dynamically.
• Update data using values from other tables.
• Delete rows based on conditions returned by another query ####Example Visits with a bill above the average bill.

Types of Subquery

1.Scalar Queries

Returns a single value: one row and one column.

Example

select
    visit_id,
    total_bill
from visits
where total_bill_kes> (
    select avg(total_bill_kes)
    from visits
);

2.Correlated subqueries

Depends on the outer query for its values.

Example

Bills above patient's average bill.

select
    v.visit_id,
    v.patient_id,
    v.total_bill
from visits as v
where v.total_bill > (
    select avg(v2.total_bill)
    from visits as v2
    where v2.patient_id = v.patient_id
);

3.Non-correlated subqueries

Does not depend on the outer query. It can be evaluated independently because it doesn't reference columns from the outer query.

Example

Visits above the hospital average

select
    visit_id,
    patient_id,
    total_bill
from visits
where total_bill > (
    select avg(total_bill)
    from visits
);

4.Nested subqueries

Is a subquery inside another subquery.

Example

Patients whose number of visits is above the average number of visits per patient

select
    patient_id,
    count(*) as total_visits
from visits
group by patient_id
having count(*) > (
    select avg(patient_visits)
    from (
        select count(*) as patient_visits
        from visits
        group by patient_id
    ) as visit_counts
);

Subqueries using IN

IN checks whether a value matches any value in a list or result set returned by the subquery.
Useful when you want to filter records based on membership in another query's results

Example

Patients who have recorded visits.

select
    patient_id,
    patient_name
from patients
where patient_id in (
    select patient_id
    from visits
);

Subqueries using EXISTS

Checks whether the subquery returns at least one row.

Example

Patients who have recorded visits

select
    p.patient_id,
    p.patient_name
from patients as p
where exists (
    select 1
    from visits as v
   where v.patient_id = p.patient_id
);

Subqueries using ANY

"ANY" compares a value against the results of a subquery. The condition is true if it matches at least one value in that result.

Example

Find visits whose bills are greater than at least one bill from a red-triage visit

select
    visit_id,
    triage_level,
    total_bill
from visits
where total_bill > any (
    select total_bill
    from visits
    where triage_level = 'red'
);

Subqueries using ALL

"ALL" compares a value against every value returned by the subquery. The condition is true only if it satisfies the comparison against all those values.

Example

Visits whose bills are greater than every red-triage visit bill.

select
    visit_id,
    triage_level,
    total_bill_kes
from visits
where total_bill_kes> all (
    select total_bill_kes
    from visits
    where triage_level = 'red'
);

Differences between CTEs and subqueries.

CTE	Subquery
Can be referenced multiple times	Typically used once.
Improves readability for complex queries.	Can become difficult to read when nested.
Optimized for multiple references.	May be less efficient for repeated operations.