SQL for beginners

Introduction

When working with databases, sometimes, we need to compare rows, combine information from different tables, perform calculations, or break complex queries into smaller, more manageable steps.

This is where SQL window functions, joins, subqueries, and Common Table Expressions (CTEs) come in.

In this article, I'll go through each concept, explain how it works, and explore some of its common uses.

1. SQL Window Functions

SQL window functions, also known as analytic functions, perform calculations across a set of rows related to the current row.

Unlike aggregate functions that typically group rows and return a single result per group, window functions return a value for each row while preserving the individual rows in the result set.

Window functions use the over() clause to define the window over which the calculation is performed. This can include partitioning, ordering, and a window frame.

The partition by clause defines the groups, while order by determines the order in which the ranking is assigned.

Types of SQL Window Functions

1. Ranking Window Functions
These functions assign rankings or numbers to rows within a window.

• Rank() — assigns a rank to each row. Rows with equal values receive the same rank, and subsequent ranks are skipped.
• Dense_Rank() — works like Rank(), but does not skip ranks after ties.
• Row_Number() — assigns a unique sequential number to every row, starting at 1 within each partition.
• Ntile(n) — divides rows into n approximately equal groups and assigns a group number to each row.

2. Aggregate Window Functions
Aggregate functions can also be used as window functions to perform calculations across related rows without collapsing them into a single row.

These include:

• Avg() — calculates the average.

• Sum() — calculates the total.
• Min() — returns the minimum value.
• Max() — returns the maximum value.
• Count() — counts rows or non-null values, depending on the expression.

3. Value and Navigation Window Functions
These functions help us retrieve values from other rows within a window.

• Lead() — retrieves a value from a subsequent row.
• Lag() — retrieves a value from a preceding row.
• First_Value() — returns the first value in the window frame.
• Last_Value() — returns the last value in the window frame.

Window functions are especially useful for rankings, running totals, comparisons, and analyzing trends while keeping individual records visible.

2. SQL Joins

SQL joins combine related data from two or more tables based on a join condition.

For example, suppose we have a doctors table and a visits table. We can use a join to retrieve doctor details alongside their visits.

Types of SQL Joins

1. Inner Join
Returns only rows that have matching records in both tables according to the join condition. Join is by default an inner join.

An inner join does not automatically remove duplicates. If multiple records match the join condition, the result can contain multiple rows for the same customer.

2. Left Join
Returns all rows from the left table and matching rows from the right table. If there is no match, the right table's columns contain null.

3. Right Join
Returns all rows from the right table and matching rows from the left table. Where there is no match, the left table's columns contain null.

4. Full Join
Returns all matching and non-matching rows from both tables. Where a row has no match in the other table, the missing columns contain null.
This is useful when we want a complete picture of the records in both tables, including unmatched records.

5. Self Join
A self join joins a table to itself. It is useful when we need to compare records within the same table or identify relationships between rows.

3. SQL Subqueries

A subquery is a query nested inside another SQL query. It allows us to use the result of one query as part of another query.

Subqueries can be used in several clauses, including:

• Where — filters rows based on the result of another query.
• Having — filters groups based on a condition that may involve a subquery.
• Select — retrieves a calculated value as an additional column.
• From — uses the result of a query as a derived table.

Types of Subqueries

Subqueries can be classified in different ways, depending on their relationship to the outer query and the shape of their results.

1. Correlated Subqueries
A correlated subquery refers to a column from the outer query. Its result depends on the current row being evaluated by the outer query.

2. Non-Correlated Subqueries
A non-correlated subquery does not depend on the outer query and can generally be executed independently.

Subqueries can also be classified by the shape of the result they return:

• Scalar subquery — returns one row and one column.
• Row subquery — returns one row with multiple columns.
• Column subquery — returns multiple rows but one column.
• Table subquery — returns multiple rows and multiple columns. Subqueries are useful for filtering data, performing comparisons, and breaking a problem into smaller logical steps.

4. SQL Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a named result set defined using the with clause. It allows us to organize a query into smaller, more readable sections.

A CTE exists for the duration of the SQL statement in which it is defined. It can be referenced multiple times within that statement.

Types of CTEs

1. Non-Recursive CTEs

These are ordinary CTEs that define a result set using a query. They are useful for simplifying complex SQL statements and making them easier to read and maintain.

2. Recursive CTEs

Recursive CTEs refer to their own results and are useful for working with hierarchical or sequential data, such as organizational structures, category trees, or relationships between parent and child records.

A recursive CTE typically consists of an initial query, called the anchor member, followed by a recursive query that continues building the result set until its stopping condition is met.

CTEs are particularly helpful when a query contains multiple calculations or intermediate steps. However, using a CTE does not automatically make a query faster; performance depends on the query structure, database engine, and execution plan.

Conclusion

SQL window functions, joins, subqueries, and CTEs each solve different problems. Understanding when and how to use these techniques makes it easier to write SQL queries that are not only correct but also clear and maintainable.
The best way to learn them is through practice.

Happy querying!