Understanding SQL Concepts

Structured Query Language (SQL) is the universal engine powering how we store, retrieve, and organize digital information. From tracking account transactions, balances and loans to storing patient data, medical history, prescriptions and appointments, almost every modern application relies on databases. Understanding core SQL concepts is a vital skill for anyone looking to turn raw data into actionable insights.

SQL Window Functions

At the heart of SQL concepts are SQL Window functions, they allow performing calculations across a set of rows that are related to the current row, without collapsing the result into a single value. They are commonly used for tasks like aggregates, rankings and running totals. SQL window functions are mainly of two types: aggregate window functions and ranking window functions.

Syntax

SELECT column_name1, 
       window_function(column_name2) 
       OVER ([PARTITION BY column_name3] [ORDER BY column_name4]) AS new_column
FROM table_name;

Aggregate Window Functions

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

SUM(): Sums values within a window.
AVG(): Calculates the average value within a window.
COUNT(): Counts the rows within a window.
MIN(): Returns the minimum value in the window.
MAX(): Returns the maximum value in the window.

AVG(): Calculates the average value within a window. It does all this without collapsing individual rows into a single summary row.

Example: Using AVG(), we will calculate the average age of patients within each gender from our hospital dataset

Unlike a GROUP BY clause—which hides the original granular details — the avg() window function allows you to retain every single record in your output while simultaneously appending the calculated average alongside it.

Ranking Window Functions

These functions provide rankings of rows within a partition based on specific criteria. Common ranking functions include:

ROW_NUMBER(): Assigns a unique number to each row in the result set.
RANK(): Assigns ranks to rows, skipping ranks for duplicates.
DENSE_RANK(): Assigns ranks to rows without skipping rank numbers for duplicates.
NTILE(): Distributes rows of an ordered partition into a pre-defined number of roughly equal groups.
LAG(): allows us to retrieve the value of a column from a previous row in the result set.
LEAD(): allows to access data of the following row, or the row after the subsequent row, and continue on.
FIRST_VALUE(): results in the first value in an ordered partition of the given data set.
LAST_VALUE(): results the last value in an ordered partition of the given data set.

RANK() Functions are used to assign ranks to rows within a group based on a specific order. Ranking functions are commonly used to organize and analyze data.

Example: Using RANK(), in our hospital dataset we will rank total bills by insurance provider

Now here is how OVER(), PARTITION BY, ORDER BY, and window frames build upon each other to isolate and calculate data.

The OVER() (The Window Opener) clause which is mandatory signals to SQL that you are creating a window function, It defines the boundaries of the rows (the "window") that the function will evaluate.

The PARTITION BY clause divides your data into distinct, independent groups (partitions) based on one or more columns.
How it works: Think of it as a GROUP BY that does not collapse rows. The window function calculates its results independently for each group, and the calculation resets entirely when a new group begins.

The ORDER BY clause defines the physical sequence of rows inside each partition. Adding an ORDER BY clause inside your OVER() statement completely changes how aggregate functions behave. Without ORDER BY, an aggregate like SUM() adds up the entire partition. With ORDER BY, SQL assumes you want a cumulative/running calculation, which triggers a default window frame.

Frames dictates precisely which rows are factored into the calculation at any given moment and are defined using either ROWS (counting exact physical rows) or RANGE (looking at matching values).

SQL Joins

Data is rarely stored in a single, massive table. Instead, it is organized into smaller, focused tables to ensure efficiency and accuracy. But how do we bring that scattered information back together when we need answers? SQL Joins, the foundational tool that allows you to connect distinct tables and unlock deeper insights from your relational databases.

SQL Joins are used to combine data from two or more tables based on a related column.

Types of SQL Joins

INNER JOIN is used to retrieve rows where matching values exist in both tables. It helps in:

Combining records based on a related column.
Returning only matching rows from both tables.
Excluding non-matching data from the result set.
Ensuring accurate data relationships between tables.

Syntax:

SELECT A.column1, A.column2, B.column1, ...
FROM tableA AS A
INNER JOIN tableB AS B
ON A.matching_column = B.matching_column;

Example of INNER JOIN to find patients and their different payment method;

LEFT JOIN is used to retrieve all rows from the left table and matching rows from the right table. It helps in:

Returning all records from the left table.
Showing matching data from the right table.
Displaying NULL values where no match exists in the right table.

Syntax:

SELECT A.column1, A.column2, B.column1, ...
FROM tableA AS A
LEFT JOIN tableB AS B
ON A.matching_column = B.matching_column;

RIGHT JOIN is used to retrieve all rows from the right table and the matching rows from the left table. It helps in:

Returning all records from the right-side table.
Showing matching data from the left-side table.
Displaying NULL values where no match exists in the left table.
Performing outer joins, also known as RIGHT OUTER JOIN.

Syntax:

SELECT A.column1, A.column2, B.column1, ...
FROM tableA AS A
RIGHT JOIN tableB AS B
ON A.matching_column = B.matching_column;

FULL JOIN is used to combine the results of both LEFT JOIN and RIGHT JOIN. It helps in:

Returning all rows from both tables.
Showing matching records from each table.
Displaying NULL values where no match exists in either table.
Providing complete data from both sides of the join.

Syntax:

SELECT A.column1, A.column2, B.column1, ...
FROM tableA AS A
FULL JOIN tableB AS B
ON A.matching_column = B.matching_column;

Self-join allows us to join a table itself. It is useful when a user wants to compare the data (rows) within the same table while Cross join allows us to join each and every row of both the tables. It is similar to the cartesian product that joins all the rows.

Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a temporary result set in SQL that you can reference within a single query. CTEs simplify complex queries, make them easier to read and can be reused multiple times within the same query. It is 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.

Syntax

WITH cte_name AS (
    SELECT query
)
SELECT *
FROM cte_name;

You can chain multiple CTEs together sequentially by separating them with a comma. The WITH keyword is only used once at the very beginning.

A recursive CTE references itself to retrieve hierarchical data, such as employee-manager relationships.

Consists of;
Anchor member: The initial query that selects the base case (e.g., top-level managers).
Recursive member: The query that references the CTE itself, pulling the next level of data.

Basic Syntax

WITH RECURSIVE cte_name AS (
    -- 1. Anchor Member
    SELECT column1, column2 
    FROM table_name 
    WHERE condition

    UNION ALL

    -- 2. Recursive Member (References 'cte_name')
    SELECT t.column1, t.column2
    FROM table_name t
    INNER JOIN cte_name c ON t.parent_id = c.id
    WHERE text_condition -- 3. Termination Condition
)
SELECT * FROM cte_name;

CTE vs. Subqueries

CTE

• Can be referenced multiple times.

• Improves readability for complex queries.

• Optimized for multiple references.

Subqueries

• Typically used once.

• Can become difficult to read when nested.

• May be less efficient for repeated operations.

SQL Subqueries

A subquery in SQL is a query nested inside another SQL query, 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.

Scalar subqueries

A scalar subquery returns exactly one value: one row and one column. Its result can be used wherever a single value is expected, such as in a comparison, calculation, or output column

Non-correlated subqueries

A non-correlated subquery is an inner query that does not depend on values from the outer query. It can execute independently because it does not reference the outer query's tables or columns.

Correlated subqueries

A correlated subquery references a column from the outer query. Therefore, its result depends on the current row being considered by the outer query.

A non-correlated subquery can run independently of the outer query, while a correlated subquery depends logically on values from the outer query. The database may optimize either query in different ways.

Nested subqueries

A nested subquery occurs when one subquery contains another subquery. This allows a query to solve a problem through several levels of conditions.

Summary of subquery placement

SELECT- Display a calculated or related value
FROM- Create an intermediate result set
WHERE- Filter records using another query's result

Subqueries using IN the IN operator checks whether a value matches any value returned by a subquery. It is especially useful when the inner query returns multiple rows. The EXISTS operator checks whether a subquery returns at least one row. It returns true when a matching row exists and false when no rows match. It is commonly used with correlated subqueries. The ANY operator compares a value with the values returned by a subquery. The condition is true if the comparison succeeds for at least one returned value. The ALL operator compares a value with every value returned by a subquery. The condition is true only when the comparison succeeds for all returned values.