Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 125 ยท Querying Data

SELECT in Depth

Master the SQL SELECT statement - choose and reorder columns, use SELECT * wisely, compute expressions and literals, and run SELECT without a FROM clause.

SELECT is the statement you'll write more than any other. Every report, dashboard, search box and product page starts with a question in the form of a SELECT. You've already used it to read whole tables; this lesson looks closely at the part right after the keyword, the select list, which decides what each row of your result looks like.

You'll see that a select list can do much more than name columns. It can reorder them, repeat them, calculate new values, add fixed labels and call functions. You'll also learn when SELECT * is handy and when it causes trouble, and how to use SELECT as a calculator with no table at all. The examples use the shop database.

The shape of a SELECT

The smallest useful query has two parts:

SQL
SELECT select_list
FROM table_name;
  • The select list says which columns the result will have.
  • The FROM clause says where the rows come from.

The result of every SELECT is itself a table: a set of named columns and zero or more rows. That's a powerful idea you'll use later, because a query's result can feed into another query.

Choosing columns

List the columns you want, separated by commas. The result contains exactly those columns, in exactly that order:

SQL Pick your columns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

LIMIT 5 keeps the output short; you'll study it in LIMIT, OFFSET and Pagination. Column order in the result follows your list, not the order in the table. You can also repeat a column, which is occasionally useful when one copy is shown as it is and another is transformed:

SQL Reorder and repeat columns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

A missing comma is the most common typo in a select list, and SQL won't always catch it. SELECT name price FROM products is valid: it means "the name column, renamed to price". You get one column instead of two, with a misleading heading. If a result has fewer columns than you expected, look for a missing comma first.

SELECT * and when to avoid it

The asterisk means "every column, in table order". It's perfect for exploring a table you don't know yet:

SQL Every column ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

In application code and saved reports, though, list the columns explicitly. SELECT * has real costs:

  • It moves more data than you need. A table with a large text or JSON column sends every byte of it across the network, even if you only show a name.
  • It breaks silently. If someone adds, removes or reorders columns, code that relied on column positions now reads the wrong values.
  • It hides intent. A reader can't tell which columns the query actually uses.
  • It blocks some optimizations. A database can sometimes answer a query from an index alone, but only when it needs no other columns. You'll learn about these covering indexes in Partial, Expression and Covering Indexes.

A good rule: * for looking, named columns for keeping.

Calculated values

A select list can contain expressions, not just column names. An expression is anything that produces a value: arithmetic, text operations, function calls or a mix. The database computes it once for each row:

SQL Compute new columns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The table doesn't contain stock_value or price_with_tax. They exist only in this result. AS gives each calculated column a readable name, called an alias. Without one, the heading is the expression text itself, such as price * stock, which is hard to use from code. Aliases get a full lesson later.

Expressions work on text too. The || operator joins strings, and functions like upper() and length() transform them:

SQL Text expressions ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The Functions and Expressions chapter covers arithmetic, string, number and date functions in depth. For now, remember that anything you can calculate from a row's values can go in the select list.

Calculations don't change the table. SELECT price * 1.2 shows a new value in the result; the stored price stays the same. Only UPDATE changes stored data.

Literal values

You can also select fixed values, called literals. Every row gets the same value:

SQL Constant columns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

This looks odd at first, but it's common in practice: labeling rows before combining two results with UNION, adding a fixed column an export file requires, or quickly testing what an expression returns.

Qualified column names

You can write a column as table.column. With one table it changes nothing, but it becomes essential once a query uses several tables that share column names, such as products.name and categories.name:

SQL table.column ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Without the table prefixes, name would be ambiguous and the query would fail. Notice that both result columns are still headed name; in real queries you would add an alias such as categories.name AS category to tell them apart. You can also write products.* to take every column of one table. You'll use this constantly in the Joins chapter.

SELECT without FROM

A SELECT doesn't need a table. Leave out FROM and you get exactly one row, which makes SELECT a handy calculator and a quick way to test functions:

SQL SELECT as a calculator

Press Run (or Ctrl + Enter) to see the result here.

Notice 7 / 2 gives 3: when both numbers are integers, SQLite (like PostgreSQL and SQL Server) does integer division and drops the remainder. Write 7 / 2.0 to get 3.5. MySQL is the exception; its / always gives a decimal result. Calculated Columns covers this in detail.

Most databases allow SELECT without FROM. Oracle (before version 23ai) requires a FROM clause, so it provides a one-row dummy table called DUAL:

SQL
-- Oracle (older versions); MySQL accepts it too
SELECT 2 + 3 * 4 FROM dual;

-- SQLite, PostgreSQL, MySQL, SQL Server, Oracle 23ai
SELECT 2 + 3 * 4;

Which part runs first?

You write SELECT before FROM, but the database logically starts with FROM: first it finds the rows, then it computes the select list for each one. Keeping this order in mind explains many rules you'll meet later:

Written order Logical order
1. SELECT 1. FROM (and joins)
2. FROM 2. WHERE
3. WHERE 3. GROUP BY
4. GROUP BY 4. HAVING
5. HAVING 5. SELECT
6. ORDER BY 6. ORDER BY
7. LIMIT 7. LIMIT

For example, because WHERE runs before SELECT, an alias defined in the select list doesn't officially exist yet when WHERE is evaluated. Standard SQL rejects WHERE stock_value > 1000 if stock_value is an alias, and PostgreSQL does too. (SQLite is lenient and accepts it, but don't rely on that if you want portable SQL.) ORDER BY runs after SELECT, so it can always use aliases. You'll see the full picture in HAVING.

Practice

Write a query that lists every product's name, its stock, and a column called above_target that contains stock - 20: how many units each product has above (or, when negative, below) a target of 20. Then add a literal column 'shop' named source.

SQL Your turn ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does SELECT price, name FROM products; return?

    The result columns follow the order of the select list, not the order in the table.
  2. 2. What does SELECT name price FROM products; (no comma) return?

    A word right after a column is read as an alias, so the query returns the name column renamed to price.
  3. 3. What does SELECT 7 / 2; return in SQLite?

    Both operands are integers, so SQLite performs integer division. Use 7 / 2.0 to get 3.5. SELECT without FROM is allowed.
  4. 4. Why is SELECT * discouraged in application code?

    Listing columns makes the query cheaper, clearer and stable when the table's structure changes.

Summary

  • The select list defines the result's columns, in the order you write them; repeating a column is allowed.
  • SELECT * returns every column. Use it to explore, but name columns in saved queries and code.
  • Select lists can contain expressions, function calls and literals, computed once per row; give them names with AS.
  • A missing comma between columns silently turns the second name into an alias.
  • table.column names a column unambiguously when several tables are involved.
  • SELECT without FROM returns one row and works as a calculator (Oracle traditionally needs FROM dual).
  • Logically, FROM and WHERE run before the select list, and ORDER BY after it.

Next: Filtering with WHERE, where you'll choose exactly which rows a query returns.