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

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.