DEV Community: Patrick Omondi Masese

# Subqueries & CTEs: Two Ways to Query Inside a Query

Patrick Omondi Masese — Tue, 29 Sep 2026 12:26:13 +0000

Sometimes one query needs the result of another query to work. SQL gives you two main tools for that: subqueries and CTEs (Common Table Expressions). They can often solve the same problem, but they read differently, and knowing when to reach for each makes your SQL a lot easier to write and debug.

What Are Subqueries?

A subquery is a query nested inside another query, wrapped in parentheses. It runs first, and its result is used by the outer query, whether that's a single value, a list of values, or an entire table-like result. Subqueries can live almost anywhere: in the SELECT list, the FROM clause, or the WHERE clause.

Using the same customer and orders tables from earlier, a subquery could find every customer whose order total is above the overall average, without you having to calculate that average separately first.

What Are CTEs?

A CTE, or Common Table Expression, is a named temporary result set defined with a WITH clause, that you can reference later in the query just like a table. It exists only for the duration of that single query, and disappears once the query finishes.

Where a subquery is nested inside another statement, a CTE is defined up front, given a name, and then used below. That structure is what makes CTEs easier to read, especially once a query needs more than one step.

Differences Between Them

Subquery	CTE
Nested inside the query that uses it	Defined separately, above the query, with a name
Can get hard to read when nested deeply	Stays readable even when chaining several steps
Cannot reference itself	Can be recursive, referencing itself for hierarchical data
Typically used once, inline	Can be referenced multiple times in the same query
No separate name required	Requires a name via WITH

Performance between the two is usually similar in modern databases, since most query planners optimize CTEs and subqueries the same way. The real difference is readability and reusability, not speed.

Practical Examples & Use Cases

A subquery in the WHERE clause filters customers whose order total beats the overall average, calculated on the fly.

SELECT customer_id, order_total
FROM orders
WHERE order_total > (
    SELECT AVG(order_total) FROM orders
);

A subquery in the FROM clause treats a query's result as if it were its own table.

SELECT category, avg_total
FROM (
    SELECT category, AVG(order_total) AS avg_total
    FROM customer c JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY category
) AS category_avgs;

The same logic as a CTE reads top to bottom instead of inside out.

WITH category_avgs AS (
    SELECT category, AVG(order_total) AS avg_total
    FROM customer c JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY category
)
SELECT * FROM category_avgs WHERE avg_total > 50;

Chaining multiple CTEs keeps a multi-step query easy to follow, each step building on the last.

WITH customer_totals AS (
    SELECT customer_id, SUM(order_total) AS total_spent
    FROM orders GROUP BY customer_id
),
top_spenders AS (
    SELECT * FROM customer_totals WHERE total_spent > 100
)
SELECT c.item_purchased, t.total_spent
FROM top_spenders t JOIN customer c ON c.customer_id = t.customer_id;

A recursive CTE, something a subquery can't do, walks a hierarchy such as a category tree.

WITH RECURSIVE category_tree AS (
    SELECT category_id, parent_id, name FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.category_id, c.parent_id, c.name
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree;

Reach for a subquery when the logic is small and used once. Reach for a CTE when you need to name a step, reuse it, chain several steps together, or walk a hierarchy recursively.

The Simple Way to Remember It

A subquery is a query hidden inside another query. A CTE is a query given a name up front, then used like a table below it. They can solve the same problems, but CTEs scale better as logic gets more complex, and only CTEs can be recursive.

If a query is starting to nest three levels deep, that's usually the sign to pull it out into a CTE.

# SQL Joins: How to Combine Tables the Right Way

Patrick Omondi Masese — Tue, 29 Sep 2026 12:13:35 +0000

Real data almost never lives in one table. Customers sit in one table, their orders in another, and the moment you need both together, you need a join. This is a practical walk through what joins are, the main types, when to reach for each one, and how they actually behave with real rows.

What Are Joins?

A join combines rows from two or more tables based on a related column between them, usually a key that exists in both. Instead of running separate queries and stitching the results together in application code, a join lets the database do that matching for you in a single query.

To make the examples concrete, imagine two tables: customer, which holds one row per customer, and orders, which holds one row per order, linked back to a customer through customer_id.

customer

customer_id	item_purchased	category
1	Running Shoes	Footwear
2	Backpack	Bags
3	Water Bottle	Accessories

orders

order_id	customer_id	order_total
101	1	59.99
102	1	24.50
103	4	12.00

Notice customer 3 has no matching order, and order 103 belongs to customer 4, who doesn't exist in the customer table. That mismatch is exactly what different join types handle differently.

Types of Joins

Join	Returns
INNER JOIN	Only rows with a match in both tables
LEFT JOIN	All rows from the left table, matched rows from the right, or NULL
RIGHT JOIN	All rows from the right table, matched rows from the left, or NULL
FULL OUTER JOIN	All rows from both tables, matched where possible, NULL where not
SELF JOIN	A table joined to itself, useful for hierarchical or comparative data
CROSS JOIN	Every row from one table paired with every row from the other

When to Use Each

INNER JOIN is the default choice when you only care about records that exist on both sides, like customers who have actually placed an order.

LEFT JOIN is for when the left table is the source of truth and you want every one of its rows represented, even if there's nothing to match on the right, such as listing every customer whether or not they've ordered yet.

RIGHT JOIN is the mirror image of LEFT JOIN. It's less commonly used in practice, since most people just swap the table order and use LEFT JOIN instead, but it's useful when the query is already structured around the right table.

FULL OUTER JOIN is for reconciliation work: finding customers with no orders and orders with no matching customer, all in one result set.

SELF JOIN comes up with hierarchical data, like an employee table where each row has a manager_id pointing to another row in the same table.

CROSS JOIN is rare in everyday querying, but useful for generating combinations, like pairing every product with every size option.

Practical Examples

An inner join returns only customers who have placed at least one order.

SELECT c.item_purchased, o.order_id, o.order_total
FROM customer c
INNER JOIN orders o ON c.customer_id = o.customer_id;

A left join keeps every customer, even ones with no orders, filling in NULLs where there's nothing to match.

SELECT c.item_purchased, o.order_id, o.order_total
FROM customer c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

A right join keeps every order, even ones whose customer_id doesn't exist in the customer table.

SELECT c.item_purchased, o.order_id, o.order_total
FROM customer c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

A full outer join surfaces every mismatch in both directions at once, useful for spotting orphaned records.

SELECT c.item_purchased, o.order_id, o.order_total
FROM customer c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

A self join compares rows in the same table, here finding customers who bought the same category as someone else.

SELECT a.item_purchased, b.item_purchased AS also_bought
FROM customer a
JOIN customer b ON a.category = b.category AND a.customer_id <> b.customer_id;

A cross join pairs every row with every other row, here generating every category and discount combination for a promo matrix.

SELECT c.category, d.discount_label
FROM customer c
CROSS JOIN discount_tiers d;

A join's ON clause decides the match. A LEFT, RIGHT, or FULL decides what happens when there isn't one. Get those two decisions right and the rest of the query usually falls into place.

The Simple Way to Remember It

INNER JOIN keeps only what matches on both sides. LEFT and RIGHT JOIN keep everything from one side no matter what. FULL OUTER JOIN keeps everything from both sides. SELF JOIN compares a table to itself. CROSS JOIN pairs everything with everything.

Start by asking which rows you can afford to lose. That answer almost always points to the right join.

# DDL, DML, DCL & TCL: SQL's Building Blocks in Plain English

Patrick Omondi Masese — Tue, 29 Sep 2026 11:49:02 +0000

A practical, no-fluff breakdown of the four SQL command categories, with real examples for each.

What is DDL?

DDL, or Data Definition Language, is the set of SQL commands used to define, modify, and remove the structure of database objects such as tables, schemas, indexes, and views. Think of DDL as the architecture layer of a database. It doesn't touch the values inside a table; it builds and reshapes the container those values live in.

A key trait of DDL commands is that they are typically auto committed. Once a DDL statement runs, the change is applied immediately and permanently, so in most systems it can't be rolled back the way DML can.

Common DDL Commands

Command	Purpose
CREATE	Creates a new database object such as a table, view, or index
ALTER	Modifies the structure of an existing object
DROP	Permanently deletes an object
TRUNCATE	Instantly removes all rows from a table while keeping its structure
RENAME	Renames an existing object

What is DML?

DML, or Data Manipulation Language, is the set of SQL commands used to work with the data inside tables: inserting new records, updating existing ones, deleting rows, and retrieving results. If DDL builds the house, DML is everything that happens inside it.

Unlike DDL, DML operations are usually transactional. Changes can be committed to save them permanently, or rolled back to undo them, as long as the transaction hasn't been committed yet.

Common DML Commands

Command	Purpose
SELECT	Retrieves data from one or more tables
INSERT	Adds new rows to a table
UPDATE	Modifies existing rows
DELETE	Removes rows that match a condition

DDL in Action

Creating a table lays out the shape data will take, column by column.

CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    item_purchased VARCHAR(100),
    category VARCHAR(50),
    discount_applied VARCHAR(3),
    purchase_frequency_days INT
);

Altering a table changes its shape after the fact, without touching existing rows.

ALTER TABLE customer
ADD email VARCHAR(255);

Dropping a table removes it, and its structure, for good.

DROP TABLE customer;

Truncating clears every row instantly, while leaving the table itself in place.

TRUNCATE TABLE customer;

Notice the pattern: every DDL statement above talks about the table itself, never about a specific row of data.

DML in Action

Inserting adds a new row to the table just created.

INSERT INTO customer (customer_id, item_purchased, category, discount_applied, purchase_frequency_days)
VALUES (1, 'Running Shoes', 'Footwear', 'Yes', 14);

Updating changes a value in an existing row.

UPDATE customer
SET discount_applied = 'No' WHERE customer_id = 1;

Deleting removes rows that match a condition, and nothing else.

DELETE FROM customer
WHERE purchase_frequency_days > 90;

Selecting reads data back out, without changing anything.

SELECT item_purchased, category, discount_applied
FROM customer
WHERE discount_applied = 'Yes';

What is DCL?

DCL, or Data Control Language, is the set of SQL commands used to control who can access or modify data. Where DDL shapes the tables and DML works the data inside them, DCL decides who is allowed to touch either one. It lives at the permissions layer of a database.

Common DCL Commands

Command	Purpose
GRANT	Gives a user or role specific privileges on an object
REVOKE	Removes privileges previously granted

What is TCL?

TCL, or Transaction Control Language, is the set of SQL commands used to manage transactions: groups of DML statements that should succeed or fail together. TCL is what makes it possible to change several rows as one unit, and undo the whole thing if something goes wrong partway through.

Common TCL Commands

Command	Purpose
COMMIT	Permanently saves all changes made in the current transaction
ROLLBACK	Undoes changes made in the current transaction
SAVEPOINT	Marks a point within a transaction to roll back to later
SET TRANSACTION	Sets properties for the current transaction, such as isolation level

DCL & TCL in Action

Granting a privilege lets a specific user query the table.

GRANT SELECT, INSERT ON customer TO analyst_role;

Revoking takes that privilege back.

REVOKE INSERT ON customer FROM analyst_role;

A transaction groups several DML statements so they commit, or roll back, together.

BEGIN;

UPDATE customer SET discount_applied = 'No' WHERE customer_id = 1;
SAVEPOINT before_delete;
DELETE FROM customer WHERE purchase_frequency_days > 90;

COMMIT; -- or ROLLBACK TO before_delete;

DDL and DML change what exists and what it holds. DCL decides who's allowed to do that. TCL decides whether the changes actually stick.

The Simple Way to Remember It

DDL defines the structure: tables, columns, and keys, and its changes usually take effect immediately. DML manipulates the data itself: the rows and values inside that structure, and those changes can typically be rolled back before they're committed. DCL controls who has access to do any of it. TCL controls whether a group of changes becomes permanent or gets undone.

If you're changing what a table looks like, that's DDL. Changing what's stored inside it, that's DML. Deciding who can do either, that's DCL. Deciding whether a batch of changes sticks, that's TCL.

Four categories, one mental model: define it, manipulate it, control access to it, commit to it.

How Excel is Used in Real-World Data Analysis

Patrick Omondi Masese — Sun, 07 Jun 2026 20:51:51 +0000

My Week 1 Reflection in Data Science & Analytics
Before starting my Data Science and Analytics course, I thought Excel was mainly used for entering data and performing simple calculations. After my first week of learning, I have realized that Excel is a powerful tool that helps people organize, analyze, and understand data in many real-world situations.

Real-World Uses of Excel
Excel is widely used across different industries to work with data and support decision-making. Some common applications include:
•Business decision-making – Companies use Excel to analyze data and track performance.
•Financial reporting – Businesses use Excel to calculate expenses, revenues, and budgets.
•Marketing analysis – Marketing teams organize and review campaign data to measure results and improve future strategies.
Excel Features and Functions I Learned
During my first week, I learned several important Excel features and functions.
Conditional Formatting
Conditional formatting helps highlight data automatically based on specific conditions. This makes it easier to identify important values and patterns within a dataset.
Formulas and Functions
I learned functions such as:
•SUM – Calculates totals.
•COUNT – Counts the number of records or values.
•AVERAGE and MODE – Help summarize data and identify common patterns.

IF and IFS – Apply logical conditions and return different results based on specific criteria.
These functions help transform raw data into useful information that can support decision-making.
Freeze Panes
I also learned how to freeze panes. This feature keeps important rows or columns visible while scrolling through large datasets, making data easier to navigate and analyze.

My Reflection
Learning Excel has changed the way I see data. Previously, I viewed data as just numbers and information stored in rows and columns. Now, I understand that data can be analyzed to answer questions, identify trends, and support decisions.
Although I have only learned the basics, I can already see how Excel plays an important role in data analysis. The skills I have gained this week provide a strong foundation for my journey in Data Science and Analytics, and I am excited to continue learning more advanced techniques in the coming weeks.
Key Takeaway
Excel is more than a spreadsheet application—it is a practical tool that helps transform raw data into meaningful insights.

How Excel is Used in Real-World Data Analysis My Week 1 Reflection in Data Science & Analytics Before starting my Data Science and Analytics course, I thought Excel was mainly used for entering data and performing simple calculations. After my first week o

Patrick Omondi Masese — Sun, 07 Jun 2026 20:36:28 +0000