DEV Community: Sam Guantai

SQL Subqueries vs CTEs: Which, When and Why

Sam Guantai — Tue, 15 Sep 2026 12:16:18 +0000

Inside you there are two wolves, one wants to use a subquery and the other prefers a CTE. Both are SQL developers, and neither will stop arguing about which one is better. Today we will discuss which wolf to feed (depending on the situation).

What Are Subqueries?

A subquery is a query nested inside another query. It usually runs first and its result gets used by the outer query either as a value to compare against, a list to check against or a table to select from.

Subqueries can show up in a few places: the WHERE clause, the SELECT list, the FROM clause, or even inside another subquery.

The 'select avg(score)' in the WHERE clause is the subquery in this case and runs first.

What Are CTEs?

A Common Table Expression (WITH ... AS (...)) sets up a temporary, named result at the top of your query, which you can then use like a table further down. It's basically a subquery you pull out and give a name to, which makes longer queries much easier to read.

Differences between Subqueries and CTEs

Subquery

Named? - No, it's just embedded inline.
Reuse - Has to be rewritten if you need it more than once.
Readability - Gets messy once you nest subqueries inside subqueries.
Where it can go - WHERE, SELECT, FROM, or nested in another subquery.

Common Table Expressions

Named? - Yes, you give it a name with WITH.
Reuse - Defined once, can be referenced multiple times in the same query.
Readability - Reads top-to-bottom as separate, labeled steps.
Where it can go - Defined once at the top, used anywhere in the main query below it.

In short, subqueries are fine for a quick, one-off check. CTEs are worth it once a query has more than one logical step and you want to name and follow each one.

Practical Examples: Why One Over the Other

Each example below is a case where the subquery and CTE aren't just two ways of writing the same thing , one is actually a better fit, whether that's for speed or for readability.

1. Comparing every row against one single value

Sometimes you need to compare every row against one specific value pulled from the same table. In our case we want to find everyone that beat Felix's score in the first test.

The plain subquery is shorter and works fine if you're only using the value once. The CTE is preferable if you need felix_score more than once in the same query (if you also want a score - felix_score as different column) naming it once avoids repeating the same subquery. For a single comparison, go with the subquery. Once you need that value more than once, pull it into a CTE.

2. Personal best score (correlated subquery vs. pre-aggregated CTE)

This is called a correlated subquery because the inner query refers back to the outer query's row (qs.member_id in our example). That means it re-runs once for every row in the outer query, not just once overall.

This is the best case for when picking a CTE is the best option over a subquery. The correlated subquery reruns its max-score lookup for every single row which is barely noticeable on a small table such as ours but as the data sets get bigger the operation get considerably slower. The CTE calculates every member's best score in one pass with GROUP BY, then joins the results back thus one calculation is done instead of many.

Wrapping Up

Three clear signs it's time to switch from a subquery to a CTE: you've written a correlated subquery that reruns for every row (pre-aggregate it instead), you're repeating the same subquery more than once in a query (name it once instead), or your query has several logical steps you want to keep readable. Outside of those cases, a short, one-time subquery is often the simpler choice.

SQL JOINS Explained

Sam Guantai — Tue, 15 Sep 2026 10:21:03 +0000

Running into Joins can be one of the most daunting and confusing parts for anyone new to SQL. In this articles we will explore what they are, what types exist, when to use them and why they matter. Lets dive in,

What Are Joins?

A join combines rows from two or more tables based on a related column between them. Relational databases like PostreSQL split data into separate tables to avoid duplication , so joins are how you stitch that data back together when you need it.

For example, you might have a customers table and an orders table. Each order has a customer id that points back to a row in customers. A join lets you show each order along with the customer's name even though that information lives in two different tables.

For our examples we will make two tables;
A customers table

An orders table

Types of Joins

1. INNER JOIN

Returns only the rows that have matching values in both tables. This is the default join type and the one you'll use most often.

You will notice that Charlie disappears (no orders) and order 4 disappears (no matching customer). Only rows present on both sides survive.

2. LEFT JOIN (LEFT OUTER JOIN)
Returns all rows from the left table and matching rows from the right table. If there's no match, the right side is filled with NULL.

In this case Charlie shows up even though he has no orders.

3. RIGHT JOIN (RIGHT OUTER JOIN)
This works as the reverse of the left join and returns all rows from the right table, plus matching rows from the left. Non-matches on the left become NULL.

Order 4 (Monitor) shows up with a NULL customer name, since customer_id = 5 doesn't exist.

In a realistic setting, RIGHT JOIN is used far less often than LEFT JOIN as most people just swap the table order and use LEFT JOIN instead, since it reads more naturally.

4. FULL JOIN (FULL OUTER JOIN)

Returns all rows from both tables, matching where possible and filling NULL where there's no match on either side.

This gives you the complete picture where everyone and everything is matched where possible.

5. CROSS JOIN

Returns the Cartesian product of two tables. Every row from the first table paired with every row from the second. No ON clause is needed (or used).

This is mostly useful for generating combinations. For example, a catalogue where every product size needs to be paired with every color available.

6. SELF JOIN

This is a table joined to itself. It is most commonly used for hierarchical or comparative data. For example an employees table where each row has a manager id pointing to another row in the same table.

So in summary

INNER JOIN - You only want records that exist in both tables; orders that have a valid customer.

LEFT JOIN - You want everything from your "main" table, even if there's no related data; all customers, including ones who haven't ordered anything yet.

RIGHT JOIN - Rare in practice; usually rewritten as a LEFT JOIN with tables swapped.

FULL JOIN - You need to see unmatched records from both sides; auditing data to find empty rows on either side.

CROSS JOIN - You deliberately need every combination of two sets.

SELF JOIN - Your table references itself; organization charts "who reports to whom."

Wrapping Up

Joins are the backbone of relational SQL. The best way to really internalize these examples is to do them for yourself, load in a couple of small tables like the ones above and run these queries. Seeing it all come together based on the JOIN used really helps to simplify the concept and the use cases become obvious.

Introduction to SQL

Sam Guantai — Mon, 20 Jul 2026 00:33:36 +0000

Starting SQL can feel daunting. It might seem like an entirely new language being thrown at you, but after approaching the core ideas it becomes relatively simple to understand. Lets start with a few definitions.

Server - This is the actual software process (like PostgreSQL or MySQL) running on a machine.
Database - It's an organized collection of data structured to be easily accessed, managed, and updated.
Schema - an organizational layer within a database, used to group related tables together.
Data - This is raw, unorganized facts and figures and can come in various formats such as numbers, text or images
DBMS (Database Management System) - is the software that enables users to create, manage, and interact with databases. Some of the most common relational DBMS options you'll encounter are: MySQL, PostgreSQL, Microsoft SQL Server

So What Is SQL Anyway?

SQL (Structured Query Language) is a standard language used to communicate with relational databases. Put simply, it's how we talk to a database and how we ask it to store, change, or hand back information.
It is further divided into sub-languages based on the exact action needed to be performed on the database. In this article we will tackle the three first and most basic sub-languages

DDL - Data Definition Language

DDL commands deal with the structure of the database. This includes creating, changing or deleting the table.
Key commands: CREATE, ALTER, DROP

DML - Data Manipulation Language

DML commands deal with the records inside the database. They modify the data without changing the structure of the table itself.
Key commands: INSERT, UPDATE, DELETE

DQL - Data Query Language

DQL command deals with reading the data inside the database. It ask questions of the data without changing anything about the data or the structure of the database.
Key command: SELECT

Data Types

Every column in a table needs a defined data type, this decides what kind of value that column can hold. Picking the right one keeps your data both accurate and efficient.

Numbers

INT — a whole number
NUMERIC / DECIMAL — numbers with fixed decimal places (useful for prices, where rounding errors are unacceptable)
SERIAL — a number PostgreSQL generates automatically for you, usually used for IDs

Text

CHAR(n) — for text that is always exactly n characters (e.g., a 10-digit phone number stored as a fixed-length code)
VARCHAR(n) — for text up to n characters (variable length, up to a maximum of n)
TEXT — for text with no fixed length limit

Date & Time

DATE — stores a date only
TIME — stores a time only
TIMESTAMP — stores both date and time together

Boolean

BOOLEAN — only two possible values: true or false

Constraints

Constraints are rules applied to columns or tables to limit the type of data that can be inserted, updated, or deleted. They're your first line of defense against messy, inconsistent data.

NOT NULL — the column can never be left empty
DEFAULT(value) — if nothing is given, this value is used automatically
UNIQUE — no two rows may share the same value in this column (e.g., two customers can't share one email address)
PRIMARY KEY — uniquely identifies each row in a table; it's essentially a combination of NOT NULL and UNIQUE
FOREIGN KEY — a column whose values must already exist as a primary key somewhere else; this is how tables link to each other
CHECK — a custom rule the value must pass, e.g., price > 0

Building A School Database

Lets build a database including what we have learned and exploring new ideas in querying, filtering and updating.

Creating the table

The SET SEARCH_PATH command ensures the data we input is sent to the relevant schema. Alternatively you can refer to the schema every time you reference the table you're working on e.g FROM greenwood_academy.students

Altering the table

Tables are rarely perfect on the first try. ALTER TABLE is DDL's way of letting you change a table's structure without dropping and recreating it.

Adding a column - the school forgot to capture phone numbers:

Renaming a column - if credit should really be called credit_hours:

Dropping a column - if phone_number turns out not to be needed after all:

Fixing and Removing Data

Once rows exist, DML gives you three tools: INSERT to input the values, UPDATE to correct existing rows and DELETE to remove them.

INSERT adds the values to the table following the constraints put while building the tables.

UPDATE changes the value of one or more columns in rows that match a condition:

DELETE removes whole rows that match a condition:

Disclaimer:The WHERE clause is doing critical work in every one of these statements. Leave it off an UPDATE or DELETE, and you'll change or remove every row in the table. Always double-check your WHERE clause before running either of these.

Filtering Rows with WHERE

WHERE is how you narrow a SELECT down to only the rows you care about, using comparison operators (=, !=, >, <, >=, <=) and logical operators (AND, OR, NOT) to combine conditions.

AND narrows your results (every condition must hold), while OR widens them (any condition can hold), mixing the two up is one of the most common beginner mistakes in SQL.

Range, Membership, and Pattern Matching

Beyond basic comparisons, SQL gives you a few operators purpose-built for common filtering patterns.

BETWEEN checks whether a value falls within an inclusive range. This mostly used for both numbers and dates:

IN and NOT IN check membership against a list of values, saving you from writing a long chain of OR conditions:

LIKE matches text patterns using % as a wildcard for any number of characters:

Note the % on both sides of 'Studies' in that last query: %Studies% matches the word anywhere in the name, while %Studies (with only a leading %) would only match names that end in "Studies"

Summarizing Data with COUNT

COUNT is an aggregate function — instead of returning individual rows, it returns a single number summarizing how many rows matched. Giving the result a readable alias with AS (like form3_students) makes the output much easier to interpret than a column literally named count.

Adding Conditional Logic with CASE WHEN

Sometimes you want a query to label rows based on a condition, without writing separate queries for each case. CASE WHEN works like an if/else chain, evaluated top to bottom for each row, and lets you create a new derived column right inside your SELECT.

Something to note from the above examples, CASE WHEN checks conditions in order and stops at the first match, so put your most specific conditions first (marks >= 80 is checked before marks >= 60).

Conclusion

SQL can feel like a lot of new vocabulary at first, but it boils down to a few core ideas:

A server hosts databases, which are organized into schemas.
A DBMS is the software managing all of this.
SQL is how you talk to it, split into sub-languages including DDL (structure), DML (data changes) and DQL (reading data).
Data types define what a column can hold, and constraints define what values are actually allowed in.
ALTER TABLE lets a schema evolve after tables already exist by adding, renaming, or dropping columns.
UPDATE and DELETE modify or remove existing rows, always guided by a WHERE clause.
WHERE, combined with comparison and logical operators, is how you filter a SELECT down to exactly the rows you need.
BETWEEN, IN/NOT IN, and LIKE handle ranges, lists, and text patterns.
COUNT summarizes matching rows into a single number, and CASE WHEN lets a query label rows with its own conditional logic.

With just CREATE TABLE, INSERT, and SELECT, you already have enough to build and query a real relational database — and with ALTER TABLE, UPDATE, DELETE, WHERE, and CASE WHEN in your toolkit, you can maintain that database and ask real questions of it.

Connecting SQL Databases to Power BI

Sam Guantai — Mon, 06 Jul 2026 00:09:33 +0000

Introduction

In any organization, Data is one of the most important assets. With Power BI we are able to turn the raw information into useful and actionable insights. By connecting SQL databases to Power BI, businesses can seamlessly access, analyze, and visualize data stored in their relational databases without the need for manual exports or complex reporting processes. Thus this integration process is an important skill for a data analyst to have. In this article, we'll explore how to connect SQL databases to Power BI and the different connection options available.

a few prerequisites

PostgreSQL installed and running
An Aiven account with a running PostgreSQL stream.

DBeaver - An open source, data management tool
PowerBI Desktop

Local Database

This is a storage system that exists on your device or a local network as opposed to a remote cloud server

Step One

Open DBeaver, Click on Database and establish a new database connection (PostgreSQL).

Fill in the required information.

Ensure you test the connection before finishing. Any errors can be detected and fixed at this point.
Inside the new database you can create a new schema where the imported data will go.
Select Input file(s) then Browse. Find the relevant data on your device and then click Proceed.

Step Two

Launch Power BI
Go to Home, then Get Data > More... > Database and select PostgreSQL database

You will be prompted to fill in the Server and database details.
Enter your Username and password when prompted and connect.

Preview your data and load it into Power BI.

Cloud-Based SQL Database

Step One

In this case you will repeat all the steps as you did with the local database but the details required when setting up a new connection will be sourced from Aiven.

Follow the same steps from the Local database section to create and import the data.

Step Two

Download the CA certificate provided in the Aiven account.
Go to your search bar and find the manage user certificates.
Follow these steps; Trusted Root Certification > Certificates (right click) > Import

Browse your files and find the certificate.
Press next on the wizard prompts till the end and Finish

Step Three

Launch Power BI
Go to Home, then Get Data > More... > Database and select PostgreSQL database

Enter the connection details from Aiven

Enter your Username and Password.
Select the table you want and proceed with cleaning or analysis.

Conclusion

And its that simple! As data continues to grow in importance, leveraging SQL databases and Power BI together provides a powerful foundation for effective analytics and reduces instances of error that is common when manually handling data.

Exploring Excel.

Sam Guantai — Sat, 06 Jun 2026 22:38:55 +0000

Microsoft Excel is a spreadsheet application used to organize, calculate, analyze, and visualize data. It allows users to store large amounts of information in rows and columns, perform calculations using formulas, and create charts and reports. In most computerized workplaces worldwide, Excel is far and away the most used application. A relatively low barrier of entry means that even the most green of workers can learn to at the very least use it as a tool for organizing company data.

Excel in real world data analytics

A majority of small to mid sized businesses rely on the insight provided by the data analyzed through Excel. A small workforce is able to analyze large amounts of data that would otherwise require a large workforce or take immense amounts of time. With the correct formulas and visualization tools the data can be used to provide a better picture into trends and even performance of employees or products that the business handles.

The IF function is particularly useful in this area because it allows businesses to automatically categorize or evaluate data based on specific conditions. For example, a company could use:

=IF(B2>=1000,"Target Met","Target Not Met")

to quickly determine whether a salesperson has achieved their sales target. This saves time, reduces errors, and makes it easier to identify areas that need attention.

-Excel is one of the most widely used tools for financial reporting and budgeting in businesses of all sizes. Accountants and financial analysts use it to record income and expenses, create detailed budgets, calculate profits and losses, and prepare financial statements. By organizing financial data into spreadsheets, companies can easily track where money is being spent and compare actual results against planned budgets.

The SUM function is particularly useful for accountants who need to balance the books and ensure that all expenses are accurately recorded. For example, if employee salaries are listed in Column B and utility expenses are listed in Column D, an accountant can calculate the total spending on both categories using the formula:

=SUM(B2:B20,D2:D20)

This formula adds together all salary and utility expenses for the accounting period, providing a combined total. By comparing this total against the company's budget or revenue, the accountant can quickly assess whether spending is within acceptable limits and identify any discrepancies that may require further investigation.

Marketing teams use Excel to evaluate the success of advertising campaigns and promotional activities. Data such as website visits, social media engagement, customer responses, and sales conversions can be organized and analyzed in spreadsheets. By identifying which campaigns generate the best results, marketers can focus their resources on the most effective strategies and improve future campaigns.

Conditional Formatting is particularly useful in marketing analysis because it allows important trends and performance indicators to stand out visually. For example, a marketing manager tracking campaign conversion rates could highlight high-performing campaigns in green and low-performing ones in red.

Thoughts one week in...

As a person who had only used Excel a handful of times before it has been quite an adventure to discover exactly how powerful a little green app on most computers can be. A single worksheet can tell the entire story of an enterprise and what seemed like a daunting amount of data can be analyzed and meaningfully presented in only a few, properly executed commands. I'm quite excited to see how much improvement I can notice in my own analysis in the coming weeks.