Retrieving Data Using the SQL SELECT Statement: Complete Guide

The SELECT statement pulls data out of one or more tables. It is the command you will run most often as a developer, analyst, or engineer, and it sits underneath almost every report, dashboard, and data pipeline in production.

When you write SELECT customer_name, credit_limit FROM customers, you are describing the result you want — two columns from a table called customers. You are not telling the database how to find it. The query planner reads your request, picks an execution strategy, and returns the rows. That is why the same query can run fast on one database and slowly on another: the data, the indexes, and the statistics are different, even though the SQL is identical.

You can query a single table, join several, filter the rows you want, group them for aggregation, sort the output, and limit how much you get back. All of that happens inside a single statement, which is why SELECT is worth understanding in depth rather than memorising a handful of examples.

You use the asterisk wildcard. The full statement is SELECT * FROM customers; and the asterisk tells the database to return every column the table currently has.

On our sample table of seven customers, that means seven rows and five columns coming back. It is the quickest way to see what is in a table when you are exploring an unfamiliar database, and it saves typing when you genuinely want everything.

That said, SELECT * has real costs, and interviewers expect you to mention them. It transfers more data across the network than you need, it forces the database to read the entire row even when a narrower index could answer the query, and it breaks silently if someone adds or renames a column. In production code, list the columns explicitly. It is a small habit that pays off every time the schema changes.

The short version is that SELECT * returns every column, while SELECT column_name returns only the ones you name. But the interesting part of this question is not the definition — it is the consequences.

When you select specific columns, the database can sometimes answer the query from an index alone, without ever reading the table. This is called a covering index, and it can turn a query that took two seconds into one that takes two milliseconds. The difference is biggest on wide tables with many columns and millions of rows.

There is also the question of stability. A query that lists columns explicitly keeps working when someone adds a new column to the table. A query that used SELECT * starts returning a different shape of data, which can break downstream code that assumed a fixed set of columns. I have seen this exact issue take down a nightly pipeline more than once.

You write DISTINCT immediately after SELECT, before the column list: SELECT DISTINCT region FROM customers;.

On our sample table, the region column contains seven values that repeat: North, South, North, West, South, West, North. After DISTINCT, you get three rows — North, South, and West. The database throws away every row that duplicates one it has already seen.

One thing worth knowing: DISTINCT applies to the entire row you select, not to a single column. If you write SELECT DISTINCT region, credit_limit, you only remove rows where both values match. Two customers in the same region with different credit limits will both come back. If that surprises you, you are not alone — it is one of the most common points of confusion in this topic.

If what you really want is the count of distinct values, wrap it: SELECT COUNT(DISTINCT region) FROM customers; returns 3.

You simply list the columns you want, separated by commas: SELECT customer_name, credit_limit FROM customers;.

There is not much syntax to learn here, but there is a habit worth building. Always ask yourself whether you actually need every column you named. If you are writing a query for a report, take the time to check which columns the report uses. If you are debugging, a narrower query is faster to read and easier to reason about.

You can also select expressions, not just columns: SELECT customer_name, credit_limit * 1.1 AS adjusted_limit FROM customers; returns a computed column. The AS clause names the new column; without it, the database generates a name like credit_limit * 1.1 which is awkward to reference later in the query.

Every database has its own way of doing this, and interviewers expect you to know at least two of them.

In Oracle, you use ROWNUM. The important detail is that ROWNUM is applied before the query is sorted, so if you want the top three orders by amount you need to sort in a subquery and apply ROWNUM outside it:

SELECT * FROM (
  SELECT order_id, order_amount FROM orders ORDER BY order_amount DESC
) WHERE ROWNUM <= 3;

In MySQL and PostgreSQL, you use LIMIT with an optional OFFSET:

SELECT order_id, order_amount FROM orders ORDER BY order_amount DESC LIMIT 3;

In SQL Server, the keyword is TOP:

SELECT TOP 3 order_id, order_amount FROM orders ORDER BY order_amount DESC;

All three queries return the same result on our sample table: order 1004 (22,000), order 1006 (18,000), and order 1001 (12,000).

One thing to always pair with row limiting is ORDER BY. Without a sort order, the database is free to return whichever rows it reaches first, which can change from one execution to the next. That behaviour is not a bug, but it will surprise you if you are not expecting it.

Use the ORDER BY clause, followed by the column name and the direction:

SELECT order_id, order_amount
FROM orders
ORDER BY order_amount DESC;

ASC is the default, so you only need to write DESC when you want descending order. You can also sort by more than one column, and you can sort by an expression or an alias from the SELECT list.

One subtlety that catches people out: NULLs are sorted somewhere in the result, but where depends on your database. In Oracle and PostgreSQL, NULLs sort last when you use ASC and first when you use DESC. If that ordering is not what you want, both databases support NULLS FIRST and NULLS LAST to override it.

Performance note: if the column you are sorting on has an index, the database can read the rows in sorted order and skip a separate sort step. On large tables, that can be the difference between a query that takes a second and one that takes a minute.

Yes, and this is one of the most useful things about SQL. You use a JOIN to combine rows from two or more tables based on a column that they share.

The most common type is INNER JOIN, which returns only the rows where the join condition matches on both sides. If you want to keep rows from the left table even when there is no match on the right, use LEFT JOIN. The mirror is RIGHT JOIN, and the union of both is FULL OUTER JOIN.

A simple example using our two sample tables:

SELECT o.order_id, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

This returns seven rows — every order, paired with the name of the customer who placed it. If any order had a customer_id that did not exist in the customers table, that order would drop out of the result because INNER JOIN only keeps matched rows.

If you want to include unmatched orders, swap to LEFT JOIN or FULL OUTER JOIN depending on what you are trying to spot.

You write the join condition in the ON clause. The clause tells the database how the two tables relate to each other, and the query returns a combined result set with columns from both.

SELECT o.order_id, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

The columns from the SELECT list can come from either table, and the ON clause specifies which column links them. In this case, the link is orders.customer_id = customers.customer_id.

You can join more than two tables, chain multiple ON conditions with AND, and mix different join types in the same query. The only rule is that the order of the joins matters — the database applies them left to right, and each join sees the result of the ones before it.

If you want to see the same query with a different join type — for instance to list every customer including those who have never placed an order — swap the table order and use LEFT JOIN:

SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

This returns all seven customers, with NULL in the order_id column where there is no order, which is exactly what you want for a "customers and their orders, including those with none" report.

A CROSS JOIN produces every possible combination of rows from the two tables. It does not need an ON clause because it does not try to match anything — it just pairs everything with everything.

SELECT c.customer_name, o.order_status
FROM customers c CROSS JOIN orders o;

On our sample data, seven customers times seven orders equals forty-nine rows. Every customer is paired with every order status, including combinations that do not make any sense.

You will rarely use this in business queries. But it has two legitimate uses: generating test data, where you want a large number of combinations to play with, and building reports where you genuinely need every pairing — for instance, a shipping schedule where every customer has to be checked against every delivery window.

One thing to be careful about: an accidental CROSS JOIN is easy to create. If you forget the ON clause in a regular join, some databases will silently treat it as a cross join and return an enormous result set. If a query suddenly returns far more rows than you expected, this is usually why.

You use the WHERE clause. It runs after FROM and before GROUP BY, so it filters individual rows before anything gets aggregated or grouped. Rows that fail the condition never make it into the rest of the query.

SELECT order_id, order_amount
FROM orders
WHERE order_amount > 5000;

On our sample table this returns five rows: orders 1001, 1003, 1004, 1006, and 1007. Orders 1002 and 1005 fall below the threshold and are filtered out.

You can combine conditions with AND, OR, and NOT, and you can use a wide range of comparison operators. The ones worth knowing by heart:

• =, <> (or !=) for equality and inequality
• >, <, >=, <= for numeric comparisons
• LIKE for pattern matching
• IN for matching a list of values
• BETWEEN for a range
• IS NULL and IS NOT NULL for NULL checks

The WHERE clause is also where the biggest performance wins live. A tight filter reduces the number of rows the rest of the query has to process, so it is worth thinking about what the database can actually push down to the storage layer rather than what looks tidy in the SQL.

The main tool is DISTINCT. Placed immediately after SELECT, it removes rows where every selected column has the same value.

SELECT DISTINCT region FROM customers;

On our sample data, this collapses the seven rows into three: North, South, and West. If you select more than one column, DISTINCT considers the entire combination — so SELECT DISTINCT region, credit_limit would only remove rows where both values match, which on this data removes nothing.

There is a second way to handle duplicates, and it is worth knowing when to reach for it instead. If you want not just the unique values but a count of how many times each one appears, use GROUP BY:

SELECT region, COUNT(*) AS customer_count
FROM customers
GROUP BY region;

This returns three rows, each with a count. DISTINCT and GROUP BY can look similar in simple cases, but their purpose is different. DISTINCT is about de-duplication. GROUP BY is about preparing rows for aggregation. Reach for the one that matches what you are actually trying to do.

GROUP BY collects rows that share the same values in the columns you name, and then lets you run aggregate functions on each group. It is how you answer questions like "how many orders has each customer placed?" or "what is the average order value per region?"

SELECT customer_id,
       COUNT(*)          AS order_count,
       SUM(order_amount) AS total_spend
FROM orders
GROUP BY customer_id;

On our sample data, this returns four rows — one per customer who has placed at least one order. Customer C001 has three orders, totalling 28,500. Customer C002 has two, totalling 7,700. C003 and C004 each have one order.

One rule you cannot break: every column in the SELECT list must either appear in the GROUP BY clause or be wrapped inside an aggregate function. If you select a column that is not grouped and not aggregated, the database does not know which value to return for each group, and it will refuse to run the query.

Common aggregates you will see alongside GROUP BY: COUNT(), SUM(), AVG(), MIN(), and MAX(). You can also use COUNT(DISTINCT column) to count unique values within each group.

HAVING filters groups after GROUP BY has produced them. That is the whole point, and it explains why WHERE cannot do the same job — WHERE runs before grouping, when the aggregates do not exist yet.

SELECT customer_id, SUM(order_amount) AS total_spend
FROM orders
GROUP BY customer_id
HAVING SUM(order_amount) > 10000;

On our sample data, this returns only customers C001 (28,500) and C004 (18,000) — the ones whose total order value exceeds 10,000. Customer C002 (7,700) and C003 (22,000) fall on the wrong side of that filter.

If you tried to write WHERE SUM(order_amount) > 10000 instead, the query would fail with an error like "aggregate functions are not allowed in WHERE." That error message is worth remembering, because it is the quickest way to remind yourself that HAVING is the right tool for filters on aggregated values.

A query can absolutely use both. A common pattern is to filter rows with WHERE first (to reduce the amount of data being grouped), then filter the groups with HAVING:

SELECT customer_id, AVG(order_amount) AS avg_order
FROM orders
WHERE order_status = 'Delivered'
GROUP BY customer_id
HAVING AVG(order_amount) > 5000;

Here WHERE keeps only delivered orders, and HAVING keeps only customers whose delivered average exceeds 5,000.

Yes, and this is where table aliases start to pay off. You name the columns you want, prefixing each with its table alias, and join the tables in the FROM clause.

SELECT c.customer_name, c.region, o.order_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

Two tables, three columns, one query. The aliases c and o keep the query readable and — more importantly — tell the database exactly which column you mean when names collide. If both tables had a column called region, you would need to write c.region or o.region to disambiguate. Without the alias, the database throws an "ambiguous column" error.

You are not limited to two tables. You can chain joins for as many tables as you need, mixing join types and adding extra conditions in each ON clause. The database applies them one at a time, in the order you wrote them.

When the query gets long enough to need several lines, keep the aliases short and consistent. I use single letters — c for customers, o for orders — because it makes the ON clause line up nicely and the columns easy to scan. Whatever convention you pick, stick with it across the codebase so readers of your queries can move between them without re-learning.

You use the LIKE operator with wildcards. There are two wildcards worth knowing: the percent sign % matches any sequence of characters, including none, and the underscore _ matches exactly one character.

SELECT * FROM customers
WHERE customer_name LIKE 'A%';

On our sample data, this returns Alice Corp — the only customer name starting with A. If I change the pattern to '%a%', I get every name containing an "a" anywhere, which is a much wider net. If I use '_a%', I get names where the second letter is "a" — that would return Diana Retail.

Two things worth remembering about LIKE. It is case-sensitive in some databases and case-insensitive in others, so if case matters, check the behaviour of yours. And if you need to search for a literal % or _, you need to escape it — usually with a backslash — because otherwise the database treats it as a wildcard.

LIKE is powerful but not always fast. If the pattern starts with a wildcard, such as '%mart', the database cannot use a normal index to answer the query and has to scan every row. If you need substring search at scale, look into full-text search features rather than LIKE.

IN checks whether a column matches any value in a given list. You put the list in parentheses after the IN keyword:

SELECT * FROM customers
WHERE region IN ('North', 'South');

On our sample data, this returns five customers — everyone whose region is North or South. It is a cleaner alternative to writing WHERE region = 'North' OR region = 'South', and for longer lists it becomes much more readable.

The list does not have to be hard-coded. You can use a subquery:

SELECT * FROM customers
WHERE customer_id IN (
  SELECT customer_id FROM orders WHERE order_amount > 10000
);

That version finds every customer who has placed an order worth more than 10,000. The subquery runs first, produces a list of customer ids, and the outer query uses that list as its filter.

One gotcha worth knowing: if the list contains NULL, or the subquery returns NULL, IN will not match anything against the NULL — not even another NULL. It is the same NULL behaviour you get everywhere else in SQL, and it is worth having it in your mental model.

BETWEEN checks whether a value falls inside a range, including both endpoints. It is shorthand for value >= low AND value <= high:

SELECT * FROM orders
WHERE order_amount BETWEEN 5000 AND 20000;

On our sample data, this returns orders 1001 (12,000), 1003 (7,500), 1006 (18,000), and 1007 (9,000). Both endpoints are included — an order worth exactly 5,000 or 20,000 would also match.

BETWEEN works with numbers, dates, and strings. Dates are where it becomes easy to get wrong. If your column stores a timestamp, and you write WHERE created_on BETWEEN '2024-01-01' AND '2024-01-31', the second value is interpreted as midnight on January 31st, not the end of the day. An order placed at 3 PM on the 31st falls outside the range and gets dropped. This is one of the most common off-by-one errors in date filtering.

The fix is either to write the bound as a half-open range — BETWEEN '2024-01-01' AND '2024-02-01' — or to compare dates rather than timestamps by casting the column. Both work; the important thing is to be aware that the trap exists.

You use IS NULL or IS NOT NULL, and you absolutely cannot use = NULL. That is the single most important thing to remember here.

SELECT * FROM customers
WHERE credit_limit IS NULL;

On our sample data, this returns Fiona Mart — the only customer with a NULL credit limit.

Why does = NULL fail? Because in SQL, NULL does not mean "empty" or "zero." It means "unknown." And an unknown value cannot be equal to anything — not even another unknown value. The expression credit_limit = NULL evaluates to "unknown" rather than "true," so no rows match. This is not a bug in your database; it is the SQL standard, and every database follows it.

The same logic applies inside IN lists, NOT IN, and comparisons with <> and !=. If a value can be NULL, be deliberate about how you handle it, and use IS NULL or IS NOT NULL explicitly wherever the behaviour matters.

If you want to give NULL a display value — for example, showing 0 instead of an empty credit limit — use COALESCE(credit_limit, 0) or, in Oracle, NVL(credit_limit, 0). Both return the first non-NULL argument from a list.

SELECT INTO creates a new table and fills it with the rows that your query returns. In SQL Server and PostgreSQL the syntax looks like this:

SELECT * INTO customers_backup FROM customers;

That creates a new table called customers_backup with the same columns and all seven rows from the original. Oracle uses a slightly different form — CREATE TABLE customers_backup AS SELECT * FROM customers; — but the effect is the same.

There are three situations where I reach for this. First, taking a quick backup before a risky data change — grab a snapshot, run the change, and drop the copy once you are confident. Second, materialising a slow query into a fast one — copy the result set into a table, add an index, and query that instead. Third, spinning off a smaller dataset for development or testing without touching production.

Two things to note. The new table does not inherit indexes, constraints, or triggers from the original — it is just the shape and the data. And if you re-run the statement with the same target name, you get an error because the table already exists. Drop it first, or use a fresh name each time.

Conditions in the WHERE clause decide which rows make it into the result. Without them, you get the whole table. With them, you get only the rows that matter for the question you are asking.

SELECT * FROM orders
WHERE order_status = 'Delivered';

On our sample data, this returns four orders — the ones marked Delivered. Simple, but the reasoning behind why you would write a query like this is the interesting part.

In real work, filters exist for three reasons. The first is correctness — a report on last quarter's sales should not include last year's numbers. The second is performance — a filtered query that scans 5% of a table will be roughly twenty times faster than the same query that scans all of it. The third is relevance — you are answering a specific question, and the extra rows are just noise the reader has to mentally filter out.

Beyond simple equality, the operators worth having on hand are >, <, >=, <=, <>, LIKE, IN, BETWEEN, and IS NULL. Combine them with AND, OR, and NOT, but be careful with mixed logic — SQL evaluates AND before OR, so parenthesise when the intent is not obvious.

You put DISTINCT right after SELECT, before the column list, and the database removes rows where every selected column has the same value:

SELECT DISTINCT region FROM customers;

On our sample data, this collapses the seven rows into three unique regions: North, South, and West.

The part that catches people out is that DISTINCT works on the entire row you select, not on a single column. If you write SELECT DISTINCT region, credit_limit FROM customers, two rows are only considered duplicates if both the region and the credit limit match. On our sample data, no two rows have that same pair, so you get all seven rows back — which is rarely what someone new to DISTINCT expects the first time.

DISTINCT is different from GROUP BY in intent, even when it produces the same output. Reach for DISTINCT when you want to remove duplicates from a result set. Reach for GROUP BY when you want to compute aggregates per group. If you find yourself using DISTINCT on a query that also has a GROUP BY, that is usually a sign that something in the query can be simplified.