SQL SELECT: columns, filters and predictable results

Your first working SELECT feels good. You type a few words, run the query, and a table appears. The next question is more useful: how do you know that these are the records you actually wanted? That is where learning SQL starts turning into analytical work.

I will take you from SELECT * FROM orders to a small report whose results you can check by hand. We will select columns, filter orders and put the result in a predictable order. Along the way, I will show how a missing value or an unresolved tie can change the answer without producing a syntax error.

All data below is synthetic. The examples target SQLite and should be run in sequence in one empty connection. A tiny dataset makes the expected answers visible. I want you to practice predicting those answers before executing the code, because that habit remains useful when the real table is far too large to inspect manually.

What SELECT * FROM table means

SELECT describes the values you want in the result. FROM identifies their source. The asterisk selects all available columns from that source. Replace table with the actual table name; the word is simply a common placeholder in explanations, not something to paste into every database query.

Think of the result as another table with named columns and some number of rows. That number can be zero. An empty result may be a perfectly valid answer, such as when no order meets your condition. It does not automatically mean that the connection failed or that somebody deleted the original records.

The ordinary SELECT statements in this tutorial read orders without changing them. A later query can select different columns from the same source. Creating the sample table and inserting its rows are separate preparation steps. I keep those operations distinct so that you can see which statements build the exercise and which statements analyze it.

The SQLite SELECT documentation describes the detailed syntax and processing rules. SQL has several dialects. The basic idea travels well, but date functions, row limits and other details vary. When an online example fails, I check the database engine and the error message before deciding that I am somehow bad at data analysis.

Set up six orders

Our table is called orders. Each row represents one order, and order_id uniquely identifies it. A customer can place several orders. That distinction matters: six orders do not necessarily mean six customers. Establishing what one row represents is one of the first things I do when approaching a new dataset.

amount is an arbitrary points value, without a currency or financial interpretation. status describes the order state, and ordered_at contains dates in a consistent year-month-day format. Every date follows the same convention in this exercise. Do not copy textual date comparisons into a messy date column without checking how its values are stored.

-- example: fixture
CREATE TEMP TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    status TEXT,
    amount INTEGER,
    ordered_at TEXT
);
INSERT INTO orders VALUES
    (1, 10, 'paid',      120, '2026-01-03'),
    (2, 10, 'cancelled',  80, '2026-01-04'),
    (3, 20, 'paid',      200, '2026-01-04'),
    (4, 30, 'pending',  NULL, '2026-01-05'),
    (5, 20, 'paid',      200, '2026-01-06'),
    (6, 40, 'paid',        0, '2026-02-01');

TEMP creates a temporary table for this connection. You do not need customer files or a production database. Running the preparation twice in the same connection will report that the table already exists. The easiest way to restart is to open another empty test connection and run the exercise from the beginning.

I deliberately included several awkward cases. Customers 10 and 20 repeat. Two amounts equal 200. One order has a zero amount, while another has no known amount. These details let us test more than the comfortable situation where every value is different and every field is complete. A useful example should expose mistakes, not merely look tidy.

Use the asterisk to inspect the source

First, I will display every column. I add ordering by the identifier to make the exercise result predictable. SELECT * FROM orders on its own does not promise a row order. Seeing the same arrangement over several runs is an observation about those runs, not a rule that your report can rely on.

-- example: all-orders
SELECT *
FROM orders
ORDER BY order_id;

You should see six rows and five columns matching the preparation. At this point I inspect names, example values and missing fields. If the table name is unfamiliar, the database client usually provides a way to inspect available tables. What you can see depends on the database and the access you have been given.

The asterisk is convenient when exploring a small unfamiliar table. For a finished report I prefer an explicit column list. That gives the recipient a defined set of fields, and adding a new source column does not automatically widen the export. Someone reading the query can also see which pieces of information were needed for the answer.

This does not mean that an asterisk is always a mistake or that removing it makes every query faster. Cost depends on the engine, storage and execution plan. My immediate concern here is the agreement between the query and its consumer: which fields should appear, in what order, and whether all of them serve a purpose.

Select columns and name the output

Now I want the order identifier, customer identifier and amount. The order of expressions in SELECT determines the order of output columns. It does not have to match the original table definition. This is a straightforward way to prepare an export in the layout expected by the person using it.

-- example: projection
SELECT order_id, customer_id, amount AS order_amount
FROM orders
ORDER BY order_id;

AS order_amount gives that output column an alias. It does not rename amount in the source table. Another independent query still refers to the original column name. An alias can explain a calculation or remove ambiguity, but I avoid abbreviations that only make sense while their author still remembers the project.

Commas separate expressions in the column list. There is no comma after the final expression before FROM. This sounds minor until a missing or extra comma consumes half an hour of your first exercise. When a syntax error points to a keyword, inspect the expression immediately before it as well as the highlighted word.

You can also place a calculation in SELECT, such as amount * 2 AS doubled_amount. Displaying the calculation does not save it back to the source. A missing amount remains missing after multiplication. Before adding that field to a real report, I would establish why doubling it answers a useful question. Correct arithmetic alone does not create a meaningful metric.

WHERE chooses rows

Suppose the question is: which paid orders have an amount of at least 150 points? I need two conditions connected with AND. One checks the status, while the other checks the amount. Before running anything, I inspect the sample: orders 3 and 5 should remain. Writing down that expectation first gives me a simple independent check.

-- example: filtered
SELECT order_id, amount
FROM orders
WHERE status = 'paid'
  AND amount >= 150
ORDER BY order_id;

I put the text value paid inside single quotes. Column names are not string literals. If you accidentally quote a column name as text, the database may compare a fixed string with another value instead of examining the column. The query can still execute successfully while answering a completely different question.

AND requires both conditions to hold. OR requires at least one. When a longer filter combines them, I use parentheses to make the intended grouping explicit. For example, selecting paid or pending orders within January should clearly group the status alternatives before applying the date restriction. A few visible parentheses are cheaper than explaining an unexpected export later.

Filtering does not delete rejected orders from the table. It restricts this query’s result. You can run the first query again and still retrieve all six records. I recommend doing that while learning, because it makes the difference between selecting data and permanently modifying data much easier to remember.

NULL and zero answer different questions

I used NULL for the unknown amount. Order 4 exists, but its amount has not been provided. Order 6 has a known amount of zero. Combining these situations without a clear business rule may produce a cleaner-looking report while removing information about the quality and completeness of the source.

To find missing values, I use IS NULL. Writing amount = NULL is not the appropriate test. Ordinary comparisons involving NULL produce an unknown result, and WHERE retains rows whose condition is true. The SQLite expression documentation explains the operator rules that underpin this behavior.

-- example: missing
SELECT order_id
FROM orders
WHERE amount IS NULL
ORDER BY order_id;

This returns only identifier 4. Replacing the condition with amount = 0 returns identifier 6. A condition of amount > 0 returns neither. The first order is excluded because its amount is unknown; the second fails the threshold despite having a known amount. The same absence from a result can therefore have different explanations.

In a practical report I often show a separate count of missing values. That helps the reader understand whether a sum or average uses the complete dataset. Replacing every missing value with zero needs an explicit reason. The existence of a function that can supply a default does not establish that the default is appropriate.

ORDER BY and LIMIT work together

Now suppose I need the two paid orders with the highest amounts. I sort by amount descending and then by the identifier ascending. That second condition resolves the tie between orders 3 and 5. It establishes a complete ordering before I limit how many records are returned to the reader.

-- example: top-two
SELECT order_id, amount
FROM orders
WHERE status = 'paid'
ORDER BY amount DESC, order_id ASC
LIMIT 2;

The output is (3, 200) followed by (5, 200). With a one-row limit, order 3 would remain. Without the second sorting expression, ordering by amount alone would not specify which of those equal orders takes precedence. This becomes especially relevant when comparing repeated exports or checking a change to a report.

LIMIT 2 means two rows, not two different amounts. It also does not mean all orders belonging to the two highest rank levels. That question needs a different rule, potentially involving a ranking function. Define the desired result precisely before choosing syntax that merely sounds similar to the wording of the request.

Limiting a result helps with exploration, but it does not guarantee a cheap query. The database may still need to inspect or sort many source rows. A display limit in your database client may also differ from a limit written into SQL. I check the settings of the actual tool and its messages about fetched records instead of assuming a universal default.

DISTINCT does not repair every repetition

Customer 20 appears in two paid orders. If I want customers who made a paid purchase, I need distinct customer identifiers. I can deliberately apply DISTINCT to that selected column. The result now describes customers, which is a different level of detail from the original order table.

-- example: customers
SELECT DISTINCT customer_id
FROM orders
WHERE status = 'paid'
ORDER BY customer_id;

The customers are 10, 20 and 40. Adding order_id to the selected columns changes the meaning: the distinct combination becomes customer plus order, so both purchases by customer 20 remain. DISTINCT compares all selected values together. It does not choose one implicitly important column and ignore the others.

I do not add it automatically after a join multiplies rows. Repetition can come from a legitimate one-to-many relationship or from a faulty join condition. Removing visible duplicates does not explain the cause. Before shortening the result, establish what each row represents and whether repeated values describe separate, valid events that the analysis still needs.

Written order and reasoning about the result

In the written query, SELECT comes before FROM, joins, WHERE, GROUP BY, HAVING, ORDER BY and the dialect’s row-limiting clause. Not every clause is required. Our examples use only part of the available language, which is enough to learn several habits that matter in larger analytical queries.

For reasoning, a different order helps: identify the source, select rows, perform any required grouping and calculations, and organize the result. This is a logical model. It is not a literal description of the physical execution plan, because the optimizer can transform how the database performs the work while preserving the query’s meaning.

When you move on to summaries, remember that WHERE filters input rows and HAVING filters groups. You do not need to force every feature into your first query. Continue with the broader introduction to SQL and databases, then use common table expressions when several calculation stages need names and clear boundaries.

Diagnose the error before changing the query

I first separate an execution error from an incorrect answer. A missing-table message calls for checking the connection, source name and preparation steps. A successfully displayed table containing the wrong customers calls for checking the query’s logic. Randomly adding conditions rarely helps establish which of those problems you actually have.

If a column is highlighted, compare its name with the table definition. A typo, missing underscore or alias used in an inappropriate place can break a query. Start again from the simple working read, add one change, and rerun it. This gives you a clear connection between the edit and its effect.

For an unexpected row count, temporarily remove the limit and inspect identifiers. The result may have been correct but only partly visible in the client. Also check whether the editor executed the whole statement or just selected text. Different tools handle selection and the current statement differently; those controls are separate from SQL syntax.

For an unexpectedly empty filter, inspect actual column values. Extra spaces or different letter case may matter. Do not immediately lowercase everything: text comparison depends on the engine and collation, while standardization can change an identifier’s meaning. Inspect a concrete record before choosing a transformation.

Finally, write down the intended question and keep the working query with its input data. “Show the best customers” is too vague to verify. “Show customers with a paid January order worth at least 150 points” gives you specific conditions to test. A screenshot without the source and query cannot reproduce the calculation. Keeping those pieces together makes a small exercise useful long after the first successful run.

Exercise: predict the answer before running it

Select the paid orders from January. Display the identifier and amount, with the most recent order date first. If dates tie, the lower identifier takes precedence. Before writing the query, note the expected number of rows and the sequence of identifiers. That small prediction makes it much harder to accept an accidental result just because it looks plausible.

-- example: exercise
SELECT order_id, amount
FROM orders
WHERE status = 'paid'
  AND ordered_at >= '2026-01-01'
  AND ordered_at < '2026-02-01'
ORDER BY ordered_at DESC, order_id ASC;

You should get identifiers 5, 3 and 1, with amounts 200, 200 and 120. Order 6 sits exactly on the upper boundary and is excluded. The interval includes its starting point and excludes its endpoint. That expresses the intended period clearly: all of January, without the first day of February.

Finally, change the status filter to a value absent from the sample. An empty result is now expected. Restore the original condition and inspect what happens when you remove ordering. Judge the query by more than whether it runs. Check its columns, row count, specific identifiers, missing values and boundaries. That is how I build confidence before a small piece of SQL becomes a recurring report.