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
FROMtable
WHERE conditions
GROUPBYgrouping
HAVING group_conditions
ORDERBY 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
ORDERBY created_atDESC;

• ASC sorts ascending.
• DESC sorts descending.

You can sort by multiple columns:

SELECT*
FROM users
ORDERBYroleASC, created_atDESC;

Step 4: Limit the number of rows

Use LIMIT to restrict how many rows are returned.

SELECT*
FROM users
ORDERBY created_atDESC
LIMIT10;

This is useful for dashboards or recent activity lists.

In SQL Server, use:

SELECT TOP10*
FROM users
ORDERBY created_atDESC;

Step 5: Group data with GROUP BY and HAVING

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

SELECTrole,COUNT(*)AS total_users
FROM users
GROUPBYrole;

This groups users by role and counts them.

To filter grouped results, use HAVING:

SELECTrole,COUNT(*)AS total_users
FROM users
GROUPBYrole
HAVINGCOUNT(*)>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 ordersON 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
LEFTJOIN ordersON 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
ORDERBY nameASC;

Example 2: Total sales per customer

SELECT users.name,
       SUM(orders.total)AS total_spent
FROM users
JOIN ordersON users.id= orders.user_id
GROUPBY users.name
ORDERBY total_spentDESC;

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'
ORDERBY created_atDESC
LIMIT5;

This uses filtering, sorting, and limiting together.

Example 4: Roles with more than five users

SELECTrole,COUNT(*)AS total_users
FROM users
GROUPBYrole
HAVINGCOUNT(*)>5
ORDERBY total_usersDESC;

Common mistakes and how to fix them

Mistake 1: Using WHERE instead of HAVING

You might write:

SELECTrole,COUNT(*)
FROM users
WHERECOUNT(*)>5
GROUPBYrole;

This fails because WHERE runs before grouping.

Correct approach:

SELECTrole,COUNT(*)
FROM users
GROUPBYrole
HAVINGCOUNT(*)>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 ordersON 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.