The Mental Model of SQL

A mental model for understanding SQL queries

Published on October 4, 2025

6 min read

SQL is a declarative language: you tell the database what you want, not how to get it. Unlike imperative programming, where you write step-by-step instructions, you describe the result you want and let the database find the best way to get there.

This mental shift is key to writing effective SQL queries and understanding how databases work under the hood.

The SQL Mindset

SQL is about describing what you want, not how to get it. The database engine handles the optimization and execution details.

What is a SQL Query?

A SQL query is a request to the database to either:

Retrieve data (SELECT)
Modify data (INSERT, UPDATE, DELETE)
Modify structure (CREATE, ALTER, DROP)

Every SQL query comes down to four questions

What data do I want? (SELECT columns)
Where does it live? (FROM tables)
What conditions must be met? (WHERE clauses)
In what shape do I want it returned? (ORDER BY, GROUP BY, etc.)

Example Query Breakdown

SELECT name, age 
FROM users 
WHERE age > 25 
ORDER BY age DESC;

Let's break it down with our mental model:

What data? → name and age columns
Where? → users table
What conditions? → Only users older than 25
What shape? → Sorted by age, highest first

Understanding JOINs

JOINs combine rows from two or more tables based on a related column. Think of them as merging tables horizontally on matching values.

The JOIN Mental Model

Imagine you have two spreadsheets:

Left table: Customer information
Right table: Order information

A JOIN creates a new "virtual table" that combines matching rows from both.

Join Type	What it does	When to use
`INNER JOIN`	Returns only rows that have matching values in both tables	When you only want records that exist in both tables
`LEFT JOIN`	All rows from left table, NULL if no right match	When you want all left records, even without matches
`RIGHT JOIN`	All rows from right table, NULL if no left match	When you want all right records, even without matches
`FULL OUTER JOIN`	All rows from both tables, NULL where no match	When you want all records from both tables

Practical JOIN Examples

-- Find all customers and their orders (including customers with no orders)
SELECT c.name, o.order_date, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

-- Find only customers who have placed orders
SELECT c.name, o.order_date, o.total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- Find all orders and their customer info (including orders with no customer)
SELECT c.name, o.order_date, o.total
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

JOIN Performance: The Database's Perspective

Knowing how databases execute JOINs helps you write faster queries.

Key Performance Factors

Factor	Impact	Optimization
Table Size	Larger tables = slower joins	Filter early with WHERE clauses
Indexes	Indexed join keys = much faster	Create indexes on foreign keys
Join Type	INNER JOINs are usually faster	Use INNER when possible
Data Distribution	Skewed data affects performance	Consider partitioning strategies

Performance Rule of Thumb

The sooner the database can reduce data, the better. That means:

Apply WHERE conditions before joining
Use indexes so we don't scan entire tables
Make sure joins use indexed columns
Filter out unnecessary data as early as possible

How Databases Actually Execute JOINs

SQL engines use different algorithms behind the scenes:

Algorithm	Best For	How It Works
Nested Loop	Small datasets, indexed joins	For each row in outer table, scan inner table
Hash Join	Large datasets, no indexes	Build hash table from smaller table, probe with larger
Merge Join	Both sides sorted	Merge two sorted inputs like merge sort

Understanding Subqueries

A subquery is a query inside another query. Think of it as asking one question to answer another.

Subquery Mental Model

Before I ask for this... go and find that first.

Subqueries break complex problems into manageable steps, where the result of one step feeds the next.

Types of Subqueries

Scalar Subqueries (Return Single Value)

-- Find the user who placed the highest order
SELECT name
FROM users
WHERE id = (
  SELECT user_id
  FROM orders
  ORDER BY total DESC
  LIMIT 1
);

List Subqueries (Return Multiple Values)

-- Find all users who have placed orders over $100
SELECT name
FROM users
WHERE id IN (
  SELECT user_id
  FROM orders
  WHERE total > 100
);

Correlated Subqueries (Reference Outer Query)

-- Find users who have placed more orders than the average
SELECT name, order_count
FROM users u
WHERE (
  SELECT COUNT(*)
  FROM orders o
  WHERE o.user_id = u.id
) > (
  SELECT AVG(order_count)
  FROM (
      SELECT COUNT(*) as order_count
      FROM orders
      GROUP BY user_id
  ) avg_orders
);

Subquery vs JOIN: When to Use Which?

Situation	Use Subquery	Use JOIN
Checking existence	`WHERE EXISTS`	`INNER JOIN`
Getting single value	Scalar subquery	Aggregation with JOIN
Complex filtering	Multiple conditions	Simple relationships
Performance	Often slower	Usually faster

Subquery Performance Considerations

Common Performance Issues

N+1 Problem: Subquery executes for each outer row
Missing Indexes: Subquery can't use indexes efficiently
Data Duplication: Subquery might process same data multiple times

Optimization Strategies

-- SLOW: Correlated subquery (executes for each user)
SELECT name, (
  SELECT COUNT(*)
  FROM orders o
  WHERE o.user_id = u.id
) as order_count
FROM users u;

-- FAST: JOIN with aggregation
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

Subquery Performance Tip

When possible, rewrite correlated subqueries as JOINs. JOINs are usually more efficient because the database can optimize the whole operation together.

Advanced SQL Concepts

Window Functions

Window functions run calculations across a set of rows related to the current row, without grouping.

-- Rank users by their total order value
SELECT 
  name,
  total_spent,
  RANK() OVER (ORDER BY total_spent DESC) as rank,
  ROW_NUMBER() OVER (ORDER BY total_spent DESC) as row_num
FROM (
  SELECT 
      u.name,
      SUM(o.total) as total_spent
  FROM users u
  JOIN orders o ON u.id = o.user_id
  GROUP BY u.id, u.name
) user_totals;

Common Table Expressions (CTEs)

CTEs make complex queries readable by breaking them into named, reusable parts.

-- Find users with above-average order counts
WITH user_order_counts AS (
  SELECT 
      user_id,
      COUNT(*) as order_count
  FROM orders
  GROUP BY user_id
),
average_orders AS (
  SELECT AVG(order_count) as avg_count
  FROM user_order_counts
)
SELECT u.name, uoc.order_count
FROM users u
JOIN user_order_counts uoc ON u.id = uoc.user_id
JOIN average_orders ao ON uoc.order_count > ao.avg_count;

SQL Query Execution Order

Knowing the logical order of SQL execution helps you debug and optimize queries:

Order	Clause	What Happens
1	`FROM`	Identify source tables
2	`WHERE`	Filter rows
3	`GROUP BY`	Group rows
4	`HAVING`	Filter groups
5	`SELECT`	Choose columns
6	`ORDER BY`	Sort results
7	`LIMIT`	Limit results

Best Practices for SQL Mental Models

Think in Sets, Not Loops

SQL operates on sets of data, not individual records. That's why:

WHERE filters entire sets
JOIN combines sets
GROUP BY creates new sets

Understand Data Relationships

Before writing queries, map out:

Which tables contain the data you need
How tables relate to each other
What the relationships mean for your query

Start Simple, Then Add Complexity

Write a basic SELECT to see your data
Add WHERE conditions to filter
Add JOINs to combine tables
Add GROUP BY for aggregations
Add ORDER BY for sorting

Use EXPLAIN to Understand Execution

Most databases provide EXPLAIN or EXPLAIN ANALYZE to show how your query runs:

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2023-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC;

Common SQL Anti-Patterns to Avoid

SELECT * in Production

-- BAD: Fetches unnecessary data
SELECT * FROM users WHERE active = true;

-- GOOD: Only fetch what you need
SELECT id, name, email FROM users WHERE active = true;

Functions in WHERE Clauses

-- BAD: Can't use indexes
SELECT * FROM orders WHERE YEAR(order_date) = 2023;

-- GOOD: Index-friendly
SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';

Nested Subqueries When JOINs Would Work

-- BAD: Nested subquery
SELECT name FROM users 
WHERE id IN (
  SELECT user_id FROM orders 
  WHERE total > 100
);

-- GOOD: Simple JOIN
SELECT DISTINCT u.name 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total > 100;

Conclusion

Mastering SQL means shifting from an imperative mindset to a declarative one. Once you see that SQL describes what you want rather than how to get it, you can write better queries and understand database performance more clearly.

Key takeaways:

Think in sets: SQL operates on collections of data
Understand relationships: Know how your tables connect
Optimize early: Filter data as soon as possible
Use the right tool: JOINs vs subqueries vs window functions
Measure performance: Use EXPLAIN to understand execution plans

Treating SQL as a declarative language for describing data relationships will serve you well as database challenges grow more complex.

Next Steps

Practice writing queries with this mental model in mind. Start with simple SELECT statements, then add complexity gradually. Use EXPLAIN to see how the database reads your queries, and always think about the data relationships first.