How to Write SELECT Queries in SQL

What you’ll build or solve

You’ll write SELECT queries that choose columns, filter rows, sort results, group data, and join tables.

When this approach works best

This approach works best when:

• You need to retrieve specific data from a relational database.
• You want to filter records based on conditions, such as active users.
• You need summaries like total sales per customer.

This guide focuses only on reading data. If you need to insert, update, or delete records, use a separate guide for those operations.

Prerequisites

• Access to a SQL database such as MySQL, PostgreSQL, SQLite, or SQL Server
• A database client or terminal
• Basic understanding of tables, rows, and columns

How SELECT queries are structured

A SELECT query is built from clauses that combine into a single statement.

Basic structure:

SELECT columns
FROM table
WHERE conditions
GROUP BY grouping
HAVING group_conditions
ORDER BY sorting
LIMIT number;

Not every query uses every clause. Include only what you need.

Clause order matters. The correct order is:

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

Now let’s build each part.

Step 1: Choose columns with SELECT and FROM

Every SELECT query starts with SELECT and FROM.

SELECT name, email
FROM users;

This returns the name and email columns from the users table.

To return all columns:

SELECT *
FROM users;

Avoid SELECT * in production when performance matters. Select only the columns you actually need.

Step 2: Filter rows with WHERE

Use WHERE to return only matching rows.

SELECT name, email
FROM users
WHERE active = 1;

You can use comparison operators:

SELECT *
FROM orders
WHERE total > 100;

Combine conditions with AND or OR:

SELECT *
FROM users
WHERE active = 1
AND created_at >= '2025-01-01';

WHERE filters rows before grouping happens.

Step 3: Sort results with ORDER BY

Use ORDER BY to control row order.

SELECT name, created_at
FROM users
ORDER BY created_at DESC;

• ASC sorts ascending.
• DESC sorts descending.

You can sort by multiple columns:

SELECT *
FROM users
ORDER BY role ASC, created_at DESC;

Step 4: Limit the number of rows

Use LIMIT to restrict how many rows are returned.

SELECT *
FROM users
ORDER BY created_at DESC
LIMIT 10;

This is useful for dashboards or recent activity lists.

In SQL Server, use:

SELECT TOP 10 *
FROM users
ORDER BY created_at DESC;

Step 5: Group data with GROUP BY and HAVING

Use GROUP BY with aggregate functions like COUNT, SUM, or AVG.

SELECT role, COUNT(*) AS total_users
FROM users
GROUP BY role;

This groups users by role and counts them.

To filter grouped results, use HAVING:

SELECT role, COUNT(*) AS total_users
FROM users
GROUP BY role
HAVING COUNT(*) > 5;

Use WHERE for row filtering and HAVING for aggregated results.

Step 6: Join multiple tables

Use JOIN to combine data from related tables.

SELECT users.name, orders.total
FROM users
JOIN orders ON users.id = orders.user_id;

This matches rows where users.id equals orders.user_id.

To include users without orders:

SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Always include an ON condition to define how tables relate.

Examples you can copy

Example 1: Active users sorted alphabetically

SELECT id, name, email
FROM users
WHERE active = 1
ORDER BY name ASC;

Example 2: Total sales per customer

SELECT users.name,
       SUM(orders.total) AS total_spent
FROM users
JOIN orders ON users.id = orders.user_id
GROUP BY users.name
ORDER BY total_spent DESC;

This combines JOIN, GROUP BY, and ORDER BY in one query.

Example 3: Recent high-value orders

SELECT id, user_id, total, created_at
FROM orders
WHERE total > 200
AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 5;

This uses filtering, sorting, and limiting together.

Example 4: Roles with more than five users

SELECT role, COUNT(*) AS total_users
FROM users
GROUP BY role
HAVING COUNT(*) > 5
ORDER BY total_users DESC;

Common mistakes and how to fix them

Mistake 1: Using WHERE instead of HAVING

You might write:

SELECT role, COUNT(*)
FROM users
WHERE COUNT(*) > 5
GROUP BY role;

This fails because WHERE runs before grouping.

Correct approach:

SELECT role, COUNT(*)
FROM users
GROUP BY role
HAVING COUNT(*) > 5;

Mistake 2: Joining without a condition

You might write:

SELECT *
FROM users
JOIN orders;

This creates a Cartesian product. Every user is combined with every order.

Correct approach:

SELECT *
FROM users
JOIN orders ON users.id = orders.user_id;

Always define how tables connect.

Mistake 3: Putting clauses in the wrong order

You might try:

SELECT *
WHERE active = 1
FROM users;

This fails because clause order matters.

Correct order:

SELECT *
FROM users
WHERE active = 1;

Remember the sequence:

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

Troubleshooting

If you see “column does not exist,” check spelling and table aliases.

If you see “ambiguous column,” qualify the column with the table name, such as users.id.

If your query returns too many rows, review your WHERE clause and join conditions.

If your query returns zero rows, double-check your filter values and date ranges.

If the database says syntax error near ORDER or WHERE, verify clause order.

Quick recap

• SELECT and FROM form the base of every query.
• WHERE filters rows before grouping.
• GROUP BY aggregates data, HAVING filters groups.
• ORDER BY sorts results.
• LIMIT restricts row count.
• JOIN combines related tables.
• Clauses combine into a single SELECT statement in a specific order.