DEV Community: Joan Wambui

CTEs, Subqueries, and Query Optimisation in SQL

Joan Wambui — Tue, 21 Apr 2026 16:54:35 +0000

If you have been writing SQL for a while, you have almost certainly run into a moment where a query starts to feel unwieldy. You need to filter based on an aggregation, or you need to reference a result you just computed, and suddenly your query is nested three levels deep and impossible to read. That is where CTEs and subqueries come in. Both solve the same core problem: referencing intermediate results within a larger query. But they do it differently, and knowing when to reach for each one will make you a sharper, more deliberate SQL writer.

Subqueries

A subquery is a query written inside another query. It is enclosed in parentheses and can appear in several places: inside a WHERE clause, inside a FROM clause, or inside a SELECT clause.

Subquery in a WHERE clause

This is the most common use. You filter the outer query based on a result computed by the inner query.

SELECT product_name, price
FROM products
WHERE price > (
  SELECT AVG(price)
  FROM products
);

The inner query runs first, computes the average price across all products, and hands that single value to the outer query. The outer query then filters using it.

Subquery in a FROM clause

Here, the inner query acts as a temporary table that the outer query reads from. This is sometimes called a derived table.

SELECT customer_name, total_spent
FROM (
  SELECT customer_id, SUM(total_amount) AS total_spent
  FROM sales
  GROUP BY customer_id
) AS customer_totals
WHERE total_spent > 5000;

Notice the alias customer_totals after the closing parenthesis. This is required in most databases when you use a subquery in a FROM clause.

Correlated subquery

A correlated subquery is one that references a column from the outer query. It re-executes for every row the outer query processes.

SELECT product_name, price, category
FROM products p
WHERE price > (
  SELECT AVG(price)
  FROM products
  WHERE category = p.category
);

Here, the inner query cannot run independently. It depends on p.category from the outer query, so it runs once per row. This is powerful but can be slow on large tables.

Common Table Expressions (CTEs)

A Common Table Expression, defined with the WITH keyword, lets you name an intermediate result and reference it by that name later in the same query. Think of it as a named, temporary result set that exists only for the duration of the query.

WITH customer_spending AS (
  SELECT customer_id, SUM(total_amount) AS total_spent
  FROM sales
  GROUP BY customer_id
)
SELECT c.first_name, c.last_name, cs.total_spent
FROM customers c
INNER JOIN customer_spending cs ON c.customer_id = cs.customer_id
WHERE cs.total_spent > 5000;

The WITH block defines customer_spending. The main query below it then joins against it as if it were a real table.

Chaining multiple CTEs

One of the most useful features of CTEs is that you can define several of them in sequence, and each one can reference the ones defined before it.

WITH product_sales AS (
  SELECT product_id, SUM(quantity_sold) AS total_qty
  FROM sales
  GROUP BY product_id
),
avg_sales AS (
  SELECT AVG(total_qty) AS avg_qty
  FROM product_sales
)
SELECT p.product_name, ps.total_qty
FROM products p
INNER JOIN product_sales ps ON p.product_id = ps.product_id
WHERE ps.total_qty > (SELECT avg_qty FROM avg_sales);

avg_sales is built directly on top of product_sales. This kind of step-by-step construction would be much harder to express clearly with nested subqueries.

Recursive CTEs

CTEs also support recursion, which is useful for querying hierarchical data such as org charts, file structures, or category trees. The CTE references itself until a termination condition is met.

WITH RECURSIVE employee_hierarchy AS (
  SELECT employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
  FROM employees e
  INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy ORDER BY level;

Recursive CTEs are one of the few things CTEs can do that subqueries fundamentally cannot replicate cleanly.

CTEs vs Subqueries: Key Differences

Understanding the difference between the two goes beyond syntax. They reflect a different way of structuring your thinking.

Readability

Subqueries, especially nested ones, are read from the inside out. The deeper the nesting, the harder it is to follow. CTEs read from top to bottom, like steps in a recipe. When a query has multiple intermediate stages, CTEs are almost always clearer.

Reusability within a query

A subquery can only be used in the one place where it is written. If you need the same intermediate result in two different parts of your query, you have to write the subquery twice. A CTE can be referenced multiple times within the same query after it is defined once.

WITH high_value_sales AS (
  SELECT customer_id, SUM(total_amount) AS total_spent
  FROM sales
  GROUP BY customer_id
  HAVING SUM(total_amount) > 10000
)
SELECT COUNT(*) AS total_high_value_customers FROM high_value_sales
UNION ALL
SELECT SUM(total_spent) AS combined_revenue FROM high_value_sales;

Performance

This is more nuanced. In most modern databases like PostgreSQL, MySQL 8+, and SQL Server, the optimiser treats CTEs and subqueries similarly in many cases. However, some databases materialise CTEs (compute and store the result once), while subqueries may be inlined and optimised as part of the surrounding query. In PostgreSQL prior to version 12, CTEs were always materialised, which could actually make them slower in some situations. From PostgreSQL 12 onward, the optimiser can choose whether to materialise or inline a CTE. The practical takeaway is: do not choose one over the other for performance reasons without testing on your specific database version.

Correlated logic

Correlated subqueries are uniquely suited to row-by-row comparisons, like checking whether a value exceeds an average within its own group. CTEs cannot be correlated in the same way because they are evaluated independently of the outer query.

When to use each

Use a subquery when:

The logic is simple and self-contained
You need correlated row-by-row comparisons
The intermediate result is only needed in one place
You want to keep the query concise for a single-step filter

Use a CTE when:

You have multiple intermediate steps
You need to reference the same intermediate result more than once
You are working with recursive or hierarchical data
Readability and maintainability matter (which is most of the time)

Optimising SQL Queries

Writing a query that returns the right answer is the first goal. Writing one that does it efficiently is the second. Here are the most impactful methods for optimising SQL queries.

1. Use indexes wisely

An index is a data structure that allows the database to find rows much faster than scanning the entire table. Always ensure that columns used frequently in WHERE, JOIN, ORDER BY, and GROUP BY clauses are indexed.

-- Without an index on sale_date, this scans every row
SELECT * FROM sales WHERE sale_date = '2023-06-01';

-- Creating an index
CREATE INDEX idx_sales_date ON sales(sale_date);

Be careful not to over-index. Every index adds overhead to INSERT, UPDATE, and DELETE operations because the index must be updated alongside the data.

2. Avoid SELECT *

Selecting all columns forces the database to read and transfer every column, even those you do not need. Always specify only the columns your query requires.

-- Avoid
SELECT * FROM customers;

-- Preferred
SELECT customer_id, first_name, last_name FROM customers;

This reduces the amount of data read from disk and transferred across the network.

3. Filter early

The further upstream you apply filters, the less data each subsequent step has to process. In joins, filter the data before joining where possible.

-- Less efficient: joins all sales, then filters
SELECT c.first_name, s.total_amount
FROM customers c
INNER JOIN sales s ON c.customer_id = s.customer_id
WHERE EXTRACT(YEAR FROM s.sale_date) = 2023;

-- More efficient: pre-filter in a subquery or CTE
WITH sales_2023 AS (
  SELECT customer_id, total_amount
  FROM sales
  WHERE EXTRACT(YEAR FROM sale_date) = 2023
)
SELECT c.first_name, s.total_amount
FROM customers c
INNER JOIN sales_2023 s ON c.customer_id = s.customer_id;

4. Avoid functions on indexed columns in WHERE clauses

Wrapping a column in a function in a WHERE clause prevents the database from using any index on that column. The function has to be applied to every row before filtering can happen.

-- This cannot use an index on sale_date
WHERE EXTRACT(YEAR FROM sale_date) = 2023

-- This can use a range index on sale_date
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'

5. Use LIMIT when exploring data

When you are investigating data or developing a query, add LIMIT to avoid accidentally scanning millions of rows.

SELECT * FROM sales ORDER BY sale_date DESC LIMIT 100;

Remove it only when you need the full result set.

6. Prefer EXISTS over IN for large subquery results

When checking whether a related row exists, EXISTS stops as soon as it finds the first match. IN collects all results from the subquery first and then checks membership. On large datasets, EXISTS is typically faster.

-- Using IN
SELECT first_name, last_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM sales);

-- Using EXISTS (generally faster on large tables)
SELECT first_name, last_name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM sales s WHERE s.customer_id = c.customer_id
);

7. Use EXPLAIN / EXPLAIN ANALYZE

Before and after any optimisation, use your database's query plan tool to understand what is actually happening. In PostgreSQL:

EXPLAIN ANALYZE
SELECT p.product_name, SUM(s.total_amount)
FROM products p
INNER JOIN sales s ON p.product_id = s.product_id
GROUP BY p.product_name;

The output shows whether indexes are being used, how many rows are being scanned, and where the most time is spent. This is the single most reliable way to confirm whether an optimisation is actually helping.

8. Avoid correlated subqueries on large tables

As noted earlier, a correlated subquery runs once per row in the outer query. On a table with 100,000 rows, that means 100,000 subquery executions. Wherever possible, rewrite correlated subqueries as joins or CTEs.

-- Correlated subquery: runs once per customer row
SELECT first_name,
  (SELECT SUM(total_amount) FROM sales WHERE customer_id = c.customer_id) AS total_spent
FROM customers c;

-- Equivalent join: far more efficient
SELECT c.first_name, SUM(s.total_amount) AS total_spent
FROM customers c
LEFT JOIN sales s ON c.customer_id = s.customer_id
GROUP BY c.customer_id, c.first_name;

9. Use appropriate JOIN types

Inner joins are generally faster than outer joins because they return fewer rows and allow the optimiser more flexibility. Use LEFT JOIN or RIGHT JOIN only when you genuinely need to retain unmatched rows.

10. Aggregate before joining where possible

If you need to join an aggregated result to another table, aggregate first and then join, rather than joining first and then aggregating. This reduces the number of rows the join has to process.

-- Join first, aggregate after (more rows to process during join)
SELECT c.first_name, SUM(s.total_amount)
FROM customers c
INNER JOIN sales s ON c.customer_id = s.customer_id
GROUP BY c.customer_id, c.first_name;

-- Aggregate first, then join (fewer rows in the join)
WITH customer_totals AS (
  SELECT customer_id, SUM(total_amount) AS total_spent
  FROM sales
  GROUP BY customer_id
)
SELECT c.first_name, ct.total_spent
FROM customers c
INNER JOIN customer_totals ct ON c.customer_id = ct.customer_id;

Putting It All Together

CTEs and subqueries are not competing tools; they are complementary. Subqueries are precise and compact for simple, single-use logic. CTEs shine when a query has multiple steps, when the same intermediate result is needed more than once, or when the goal is to write something a colleague can read and understand without guessing. Query optimisation, meanwhile, is not about memorising a checklist; it is about understanding what the database is doing and removing unnecessary work at every stage. The combination of clean, readable structure and deliberate performance thinking is what separates functional SQL from genuinely good SQL.

SQL fundamentals: DDL, DML, filtering, and CASE WHEN

Joan Wambui — Mon, 13 Apr 2026 16:06:42 +0000

What is SQL?

SQL (Structured Query Language) is the standard language for interacting with relational databases. Whether you are storing customer records, tracking exam results, or analysing transactions, SQL is how you talk to the database.

The five command categories

SQL commands are grouped into five categories:

Category	Its Function	Commands
DDL (Data Definition Language)	Defines and modifies the database structure	CREATE, ALTER, DROP, TRUNCATE
DML (Data Manipulation Language)	Manages/manipulates data inside tables	INSERT, UPDATE, DELETE
DQL (Data Query Language)	Retrieves data from the tables	SELECT
TCL (Transaction Control Language)	Manages transactions	COMMIT, ROLLBACK, SAVEPOINT
DCL (Data Control Language)	Controls access and permissions	GRANT, REVOKE

This article focuses on DDL, DML, and DQL.

DDL (Data Definition Language)

DDL creates the schemas, tables and columns. It defines the shape of your data before any data exists. It has four commands, as mentioned in the table: CREATE, ALTER, DROP and TRUNCATE.

CREATE TABLE sets up a table with its columns, data types, and constraints. ALTER TABLE modifies an existing table by adding columns, renaming them, or dropping ones that are no longer needed. TRUNCATE TABLE removes the contents of the table; however, it retains the table structure. DROP TABLE removes the entire table.

A lightbulb moment: CREATE TABLE wraps its column definitions in parentheses. ALTER TABLE does not as the instruction follows directly after the table name.

-- Creation of a student table
CREATE TABLE students (
    student_id    INT PRIMARY KEY,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL,
    gender        VARCHAR(1),
    date_of_birth DATE,
    class         VARCHAR(10),
    city          VARCHAR(50)
);

-- Addition of a phone number column
ALTER TABLE students
ADD COLUMN phone_number VARCHAR(20);

DML (Data Manipulation Language)

INSERT INTO adds rows to a table. You list the target columns, follow with VALUES, and can insert multiple rows in one statement by separating them with commas. Always quote dates. Without quotes, PostgreSQL reads 2008-03-12 as arithmetic (2008 − 3 − 12 = 1993).

INSERT INTO students (student_id, first_name, gender, date_of_birth, class, city)
VALUES
    (1, 'Amina', 'F', '2008-03-12', 'Form 3', 'Nairobi'),
    (2, 'Brian', 'M', '2007-07-25', 'Form 4', 'Mombasa');

plaintext

UPDATE modifies existing rows. DELETE removes them. Both require a WHERE clause. Without it, every row in the table is affected, not just the one you intended.

-- Updating a student's current city of residence:
UPDATE students
SET city = 'Nairobi'
WHERE student_id = 5;

-- Deleting an exam result from a student whose id is 9:
DELETE FROM exam_results
WHERE result_id = 9;

plaintext

WHERE clause

SQL has a variety of operators that allow for various filtering conditions. The WHERE clause, when used with these operators, allows you to target specific rows.

Some operators include:

BETWEEN filters a range and is inclusive on both ends.
IN checks against a list of values. It is cleaner than chaining multiple OR conditions.
LIKE matches text patterns, where % represents any sequence of characters.

WHERE marks BETWEEN 50 AND 80
WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu')
WHERE class NOT IN ('Form 1', 'Form 2')
WHERE first_name LIKE 'A%' OR first_name LIKE 'E%'

plaintext

CASE WHEN

CASE WHEN lets you apply conditional logic directly inside a query, generating a new column based on rules you define — without touching the underlying table.

-- Exam results labelling:

SELECT *,
    CASE
        WHEN marks >= 80 THEN 'Distinction'
        WHEN marks >= 60 THEN 'Merit'
        WHEN marks >= 40 THEN 'Pass'
        ELSE 'Fail'
    END AS performance
FROM exam_results;

PostgreSQL evaluates conditions top to bottom and stops at the first match, so order matters. The ELSE clause covers anything that falls outside your defined conditions. Without it, unmatched rows return NULL.

In CASE WHEN, the result exists only in the query output. The table structure is never changed.

What makes CASE WHEN powerful is that it lets you enhance your data at query time without needing extra columns in your schema. For reporting and analysis, that flexibility is very useful.

How I Published My Power BI Report and Embedded It on a Website

Joan Wambui — Sun, 05 Apr 2026 22:07:04 +0000

I recently completed a Power BI project, an electronics sales dashboard built from a raw CSV file. Cleaning the data, modeling the tables, writing DAX measures, all of that happened in Power BI Desktop.

This article covers what happens next: getting that report out of your computer and onto the internet where it can actually be used.

1. Create a Workspace

Before publishing anything, you need a workspace. In Power BI Service, a workspace is essentially a folder that holds your reports and datasets.
Go to app.powerbi.com and sign in. On the left navigation panel, click Workspaces, then click “New workspace”.

2. Publish from Power BI Desktop

Go back to Power BI Desktop, press Ctrl + S to save your file, then go to the Home tab in the ribbon and click Publish on the right side.

A dialogue box appears asking where to publish. Select the workspace you just created and click Select.

3. Generate the Embed Code

Now that the report is live in Service, you need the embed code to place it on a website.
With the report open in the browser, click File in the top menu. Hover over Embed report and select Publish to web (public).

You can also embed the report using the other option “Website or portal” copy the iframe code

4. Embed It in a Webpage

Open any HTML file in VS Code or a text editor of choice. Paste the iframe code where you want the report to appear. Save the file and open it in a browser.

Your report is now published and the link is ready to share.

Understanding Data Modeling in Power BI: Joins, Relationships, and Schemas Explained

Joan Wambui — Tue, 31 Mar 2026 03:15:27 +0000

What Is Data Modeling?

Data modeling refers to how one organises and connects their data so it can be analysed correctly. In Power BI, it defines how tables relate to each other, how filters move across visuals, and how your measures calculate results.

Joins

Joins combine data from two tables based on a shared column. In Power BI, joins happen in Power Query Editor before data enters the model.
INNER JOIN - Returns only rows with a match in both tables.
LEFT JOIN - Returns all rows from the left table only.
RIGHT JOIN - Returns all rows from the right table only.
FULL OUTER JOIN - Returns everything from both tables. Useful for spotting data gaps.
LEFT ANTI JOIN - Returns only rows from the left table with no match on the right.
RIGHT ANTI JOIN - Returns only rows from the right table with no match on the left.

Power BI Relationships

Relationships connect tables inside the model without merging them. They control how filters move between tables when you interact with visuals.
Cardinality defines how rows relate across tables:
• One-to-Many (1:M) - One customer, many orders. The most common type.
• Many-to-Many (M:M) - Requires careful handling, best resolved with a bridge table.
• One-to-One (1:1) - Usually means the tables can be merged.

Active vs Inactive relationship - Only one active relationship is allowed between two tables. Inactive relationships are triggered using USERELATIONSHIP() in DAX when needed.

Cross-filter direction - Single direction is the default and safest. Bidirectional filters flow both ways but can cause performance issues if overused.

Joins vs Relationships

Fact Tables vs Dimension Tables

Fact tables hold measurable data such as sales and transactions. They are long with many rows and link out to dimension tables via foreign keys.
Dimension tables hold descriptive data such as customer names, product categories, dates. They give context to the numbers in your fact table.
In simple terms, your fact table is the center. Dimension tables surround it.

Schemas

Star Schema - One fact table connected directly to multiple dimension tables. Fast, clean, and what Power BI is optimised for. This should be used by default.
Snowflake Schema - Dimension tables broken into sub-dimensions. This is more complex and slower in Power BI. It is recommended for one to use only it when source data is already normalised.
Flat Table - Everything in one table. This is simple but it creates redundancy and performance problems at scale.
Pro Tip:

A clean data model is what makes a Power BI report trustworthy. Start with a star schema, separate your facts from your dimensions, define your relationships carefully, and always build a proper Date table. The model is invisible to the end user, but it is what everything depends on.

How Linux is Used in Real-World Data Engineering

Joan Wambui — Mon, 30 Mar 2026 02:46:26 +0000

Linux has been such a common word in the tech journey, a mountain of a word for beginners, but a simple and helpful foundation once you grasp it. I had heard it before but couldn't tell you what it actually was. Was it a programming language? A tool? The same thing as Bash or Git? For a while I used all four interchangeably. A quiet kind of confusion, the type you don't realise you have until something breaks and you don't know where to look.

The moment it clicked wasn't dramatic. It was sitting at a terminal during my data engineering class, connected to a remote server, and realising the environment I was operating in was Linux. Not a language. Not a tool I had installed. The environment itself.

What Actually Is Linux?

Linux is an operating environment, similar to Windows or macOS, that sits beneath your tools, files, terminal sessions, and pipelines. Bash is the language you speak inside it. Git is a version control tool that runs inside it. GitHub is the cloud platform where your Git repositories live. Four different things, four different layers, blurring together because you encounter them all at once through the same black terminal window.

This matters because data engineering assumes Linux fluency without announcing it.

How Linux Shows Up in Real Data Engineering Work

When you provision a virtual machine on Azure or AWS, you land in a Linux environment. When your pipeline runs on a scheduler, it is running on a Linux server. When you work with Docker containers, they are built on Linux. It is rarely the thing being discussed, but it is always underneath the thing being discussed.

In practice, data engineers use Linux to navigate directories where raw data lands and processed outputs go. They write Bash scripts to automate repetitive ingestion tasks (which include ingesting a file, validating its structure, moving it to a staging folder, logging the result) and schedule them with cron to run automatically.

# Step 1: Navigate to where raw data lands
cd /data/raw

# Step 2: Run your ingestion script
bash ingest.sh

# Step 3: Schedule it to run automatically every day at 6am
0 6 * * * /scripts/ingest.sh

They monitor running jobs, check resource usage, and kill processes that hang. When something breaks on a production server, Linux is the environment you are debugging in. Knowing it is not optional.

Linux will not be the most exciting thing you learn in data engineering. It does not have a sleek interface or a compelling pitch. But it is the ground where real data work happens, on servers, in the cloud, inside containers, underneath every tool you will eventually depend on. The clearer your understanding of what it is, the faster everything else makes sense.