SELECT in SQL: columns, calculations and readable output

You write SELECT *, get a table back, and everything seems fine for a moment. Then somebody asks what one row represents, why an amount appears twice, and whether the first order is the oldest. Retrieving data does not answer those questions by itself. I start learning SELECT by deciding what result I want to hand to another person.

I will use a handful of synthetic orders. All examples target PostgreSQL 18. You do not need a company database or a large dataset to follow the reasoning. You need to read a result and explain where each column came from. That is a useful starting point for working with SQL.

SELECT defines the shape of the output

A SELECT statement retrieves data, and its select list defines the output columns. These can be source columns, calculations, or constants. FROM identifies the source. The PostgreSQL documentation describes these separate roles in its section on select lists.

For me, the practical question is whether the reader needs every order detail or just an identifier and an amount. That choice already affects how readable the report will be. If I provide fifteen technical fields, I leave the reader to work out which ones matter. Choosing columns is part of answering the question.

A source does not have to be one physical table. It can also be a view, a subquery, or the output of a join. I will stay with one source here so that choosing columns does not get tangled up with combining data. Joins become easier to reason about once you can explain what a single input row represents.

A dataset small enough to inspect completely

Suppose our source, orders, contains the following records. Amounts use arbitrary units, and every record was invented specifically for this explanation.

Quantities and prices for the same orders appear below. The identifier connects both parts of the display; the source still has four rows.

One row represents an order containing one kind of product. A real shop may have several lines per order. In that case, quantity multiplied by price describes a line, and the order total still needs to be assembled. I state that assumption before calculating anything: correct arithmetic can still produce a misleadingly named metric.

To reproduce the source in a PostgreSQL editor, you can use the following statement. WITH gives a name to a query result, while VALUES supplies its rows. The VALUES documentation explains how to build a small constant table without creating a persistent table on disk.

WITH orders(order_id, customer_name, city, quantity, unit_price) AS (
    VALUES
        (1, 'Ada', 'Krakow', 2, 10.00),
        (2, 'Ben', 'Gdansk', 1, 30.00),
        (3, 'Ada', 'Krakow', 3, 10.00),
        (4, 'Celina', 'Krakow', 1, NULL)
)
SELECT order_id, customer_name
FROM orders
ORDER BY order_id;

The later examples assume the same orders source is available. When using the CTE above, keep the WITH section and replace the query that follows it. The CTE name belongs to one statement. Finishing that statement with a semicolon does not leave a permanent table behind for your next query.

Read your first query as a request

SELECT order_id, customer_name
FROM orders
ORDER BY order_id;

The output contains these pairs in sequence: 1, Ada, 2, Ben, 3, Ada, and 4, Celina. Selecting two columns preserved all four rows. No condition excluded an order, and no grouping combined records into a summary. The query simply changed what information was visible for each order.

I read it as follows: take the order identifier and customer name from orders, then display them by order identifier. The list after SELECT sets the column order. Swapping the two column names puts the customer on the left, but it does not change the meaning of the underlying record.

Commas separate expressions in the list. I do not add a comma between the final expression and FROM. A semicolon finishes the statement. I write keywords in uppercase and field names in lowercase because the structure is easier to scan. That convention improves readability; it is not a requirement that SQL keywords must use capitals.

Why I limit the use of SELECT star

SELECT *
FROM orders
ORDER BY order_id;

The star returns every column exposed by the source. Our example produces five columns. This is convenient when exploring an unfamiliar table: I can inspect its fields and decide which ones I need. For a query that becomes a report or an export, I prefer an explicit list.

The reason is practical. A column list is an agreement with the consumer of the result. If a report needs an order number and a city, I want that requirement visible in the query. Adding a new source field should not accidentally widen an established export. An explicit list also helps explain why a particular field is being shared at all.

I am not promising a specific speed improvement. Runtime depends on the database, execution plan, and data. Here I care about predictable output. Replacing the star with three names does not establish that the entire query performs better. A performance claim needs a separate examination of what the database actually does.

Use aliases to give columns useful names

SELECT
    order_id AS order_number,
    customer_name AS customer
FROM orders
ORDER BY order_number;

The values stay the same, but the output headings become order_number and customer. An alias helps present a field clearly and gives a calculation a name. It does not rename a stored column or rebuild the source table. Another query can still refer to the original order_id field.

PostgreSQL allows an output column alias in ORDER BY, but not in WHERE at the same query level. The SELECT reference describes that distinction. When I need an alias to become input to another stage, I can put the calculation in a subquery and refer to its output from the outer query.

I choose names that do not require somebody to decode private abbreviations. value2 offers little help. line_amount at least suggests an amount for one line. The name still needs a definition: if the real data includes tax, currencies, or adjustments, the report should say how those are handled. A readable alias cannot supply missing business rules.

A SELECT calculation works on each input row

SELECT
    order_id,
    quantity * unit_price AS line_amount
FROM orders
ORDER BY order_id;

For the first row, two multiplied by ten gives 20.00. The second row gives 30.00 because it contains one unit at thirty. Three units at ten also give 30.00 for the third row. The last row has no known price, so its amount remains NULL. Your client may display trailing decimal zeroes differently.

PostgreSQL multiplication operates on numeric types. These example prices are decimal numbers. I am not extending the example to arbitrary text fields containing a price followed by a unit label. Such an import needs preparation before this calculation has a reliable meaning.

The calculation does not write the result back to the source. It produces a value for the query output. That matters when you are starting out: you can compare several expressions without adding a new stored column every time. The statements in this tutorial are simple queries that read data and produce results.

Keep an unknown value visible

The fourth record says that the price is unknown. I have no basis for replacing it with zero. Zero would describe a known price equal to zero, which leads to a different interpretation. In a report I could separately show how many lines lack a price and explain that an amount total does not cover the complete dataset.

The first skill is recognising a missing value. An apparently blank cell in your query editor might be the client’s display format for NULL. Before assuming it is an empty string, I check the column type and the tool’s display settings. The way a result grid looks does not settle the meaning of the value it contains.

If you need replacement values, read the entry on COALESCE. I do not automatically wrap every calculation in that function. First I decide whether the proposed replacement follows from the question being answered. Hiding a missing input can make a table easier to read while making its conclusion harder to defend.

Identical output rows can represent different orders

SELECT customer_name, city
FROM orders
ORDER BY customer_name, city;

Ada and Krakow appear twice because Ada has two orders. Removing order_id from the display did not merge them. It removed the information that made those events easy to distinguish. The resulting repetition is entirely consistent with our source table.

This is a common trap when judging data quality. A repeated customer name can simply represent another valid event. Before calling a record a duplicate, I define its key and level of detail. In this dataset, order_id identifies an order. The customer name does not serve that purpose, even though it is often more familiar to the reader.

If I need a list of different customer and city pairs, I add DISTINCT. The result contains three pairs: Ada with Krakow, Ben with Gdansk, and Celina with Krakow. DISTINCT compares the whole selected row. Adding the order identifier makes all four output records distinct again. See the SELECT DISTINCT entry for a closer explanation.

ORDER BY sets the order and LIMIT caps the count

Without ORDER BY, PostgreSQL does not guarantee the order of returned rows. Seeing the same arrangement several times does not make it a promise. The sorting documentation separates producing a result from putting that result in a defined order.

SELECT order_id, quantity
FROM orders
ORDER BY quantity DESC, order_id
LIMIT 2;

Order 3 comes first with a quantity of 3. Order 1 follows with a quantity of 2. The second sorting key resolves any ties in quantity. Since order_id uniquely identifies orders in this dataset, it provides a final way to determine their sequence when quantities are equal.

LIMIT 2 means at most two rows. If a condition leaves only one row, the output contains one. LIMIT does not itself find the two largest values; ORDER BY determines what comes first. The LIMIT reference also explains why a limit without a predictable ordering can select an unpredictable subset.

SELECT and WHERE answer different questions

SELECT order_id, customer_name, quantity
FROM orders
WHERE city = 'Krakow'
ORDER BY order_id;

This result has three records: orders 1, 3, and 4. SELECT defines the visible fields, while WHERE admits the records satisfying the condition. The city participates in filtering even though it is not displayed. I do not need to expose every column used by the query’s condition.

It helps to describe these choices separately. “Show only customers from Krakow” concerns rows. “Show only the number and customer” concerns columns. When a report request is vague, those two questions let me clarify the requirement without guessing. They also provide a useful way to explain a query to somebody who does not write SQL.

A text value goes in single quotes. The column name city is an identifier. PostgreSQL uses double quotes for quoted identifiers, so replacing the single quotes with double quotes changes the meaning. The lexical structure reference documents those rules. I keep the example names simple so punctuation does not obscure the main idea.

SELECT can return a constant or a simple calculation

In PostgreSQL, I can write SELECT 2 + 3 AS total; without FROM. It returns one row containing 5. This helps explain that a select list contains expressions. A source column is one possible expression, but it is not the only option. I do not need a table containing the numbers two and three.

A constant can also sit next to source data. Adding 'example' AS dataset_label to our query gives each of the four rows the label example. Single quotes identify a text value. Writing example without those quotes makes the database look for that name in the available scope. The two versions look similar but make different requests.

I do not use a constant label to conceal a source limitation. Writing 'complete' does not make Celina’s missing price known. Such a field should describe the actual origin or state of the data. A polished report heading does not prove completeness. That is why I explicitly call the labels in this dataset examples.

It also helps to distinguish a column alias from a source alias. FROM orders AS o allows me to write o.order_id in the select list. The short name o identifies where the field comes from; AS order_number sets its output name. The latter does not replace the former everywhere in the statement. This distinction becomes especially useful when two tables both contain an id column.

The output type matters as much as its appearance

A number and a string of digits can look identical in a result grid. In a product code such as 0012, the leading zeroes may be part of the identifier. Treating it as a number can remove information required for matching another system. I therefore do not convert every field containing digits into a numeric type.

Fields used for calculations need a deliberate type as well. The PostgreSQL numeric type documentation distinguishes integers, exact decimal numbers, and approximate floating-point numbers. You do not need every detail at the beginning. You do need to know that the database does not store every value in one interchangeable format.

Division makes the difference visible. In PostgreSQL, SELECT 5 / 2 AS result; returns 2 because both arguments are integers. SELECT 5.0 / 2 AS result; returns a numeric value equal to 2.5. These expressions use different types even though a reader might expect the same arithmetic. The mathematical operators reference linked earlier documents integer division.

If you later calculate the share of orders from Krakow, you do not want a zero caused by integer division. Before applying percentage formatting, make sure the expression itself produces the intended value. Displaying two decimal places cannot restore the fractional part of a result that was already calculated as an integer.

You do not need to memorise all PostgreSQL types now. Build one habit instead: for a new field, establish what it means, how it is stored, and what operations you intend to perform. A minute spent on that decision can prevent a long search through later layers of a report.

Check the meaning before trusting the table

I begin with one sentence: each output row represents one order from Krakow. Then I compare that statement with the selected columns and condition. If the identifier is gone, I ask whether I can still distinguish events. If DISTINCT appears, I check whether I deliberately changed the result’s level of detail.

Next I choose one source record and describe its path to the output by hand. Order 3 passes the city condition, displays Ada as the customer, and has a quantity of 3. In the amount example, it produces 30.00. A small reference point like this can expose a mistaken field or assumption before a large result becomes distracting.

Finally I read the names and units from the consumer’s perspective. A quantity column needs a definition: pieces, packages, or hours are different things. SQL will follow the statement’s syntax. I remain responsible for whether the resulting table can be interpreted honestly and whether its labels match the actual calculation.

An exercise and a useful next step

Write a query showing order_id, customer_name, and quantity * unit_price AS line_amount for Krakow, ordered by identifier. The expected output contains three rows, with amounts 20.00, 30.00, and NULL. Before looking for a ready-made answer, write those rows down from the small source table.

Then remove the identifier and amount, leaving the customer and city. Explain why Ada still appears twice. Only then add DISTINCT and name the new level of detail: a different customer and city pair. Understanding that change in meaning matters more than remembering another keyword. It is also an explanation you can give without opening a database editor.

Continue with WHERE filtering and the logical order of SQL clauses. Once you understand where rows and columns come from, grouping and joins become easier to assess. You have a concrete reference for evaluating the next result, rather than a collection of phrases to paste into an editor.