Skip to content
elephantoo

SELECT: reading data

Lesson 6 of 31 12 min read

Choose columns, compute expressions, rename with aliases, remove duplicates and limit results.

SELECT is the statement you'll write more than any other. It asks the database a question and gets back a result set, a temporary table of rows and columns. In this lesson you'll choose columns, compute new ones, rename them, remove duplicates and limit how many rows come back.

Sample data#

Run this once to follow along:

SQL
CREATE TABLE books (
    id        INT AUTO_INCREMENT PRIMARY KEY,
    title     VARCHAR(100) NOT NULL,
    author    VARCHAR(60)  NOT NULL,
    genre     VARCHAR(30)  NOT NULL,
    price     DECIMAL(6,2) NOT NULL,
    pages     INT          NOT NULL,
    published YEAR         NOT NULL
);

INSERT INTO books (title, author, genre, price, pages, published) VALUES
    ('Clean Code',             'Robert Martin',   'programming', 34.99, 464, 2008),
    ('The Pragmatic Programmer','Hunt & Thomas',  'programming', 42.50, 352, 2019),
    ('Dune',                   'Frank Herbert',   'sci-fi',      12.99, 688, 1965),
    ('Project Hail Mary',      'Andy Weir',       'sci-fi',      16.00, 496, 2021),
    ('Sapiens',                'Yuval Harari',    'history',     18.75, 512, 2011),
    ('The Martian',            'Andy Weir',       'sci-fi',      10.50, 384, 2011);

The basic shape#

SQL
SELECT column1, column2
FROM table_name;

For example:

SQL
SELECT title, author FROM books;
Output
+--------------------------+---------------+
| title                    | author        |
+--------------------------+---------------+
| Clean Code               | Robert Martin |
| The Pragmatic Programmer | Hunt & Thomas |
| Dune                     | Frank Herbert |
| Project Hail Mary        | Andy Weir     |
| Sapiens                  | Yuval Harari  |
| The Martian              | Andy Weir     |
+--------------------------+---------------+

Selecting everything#

SQL
SELECT * FROM books;

* means "every column". It's handy while exploring, but in application code you should list the columns you need. That's clearer, sends less data over the network, and your code won't break when someone adds a column.

Expressions and computed columns#

A SELECT list can hold any expression, not just column names: arithmetic, function calls, text and more.

SQL
SELECT
    title,
    price,
    price * 0.9            AS sale_price,
    ROUND(price / pages * 100, 2) AS cents_per_page,
    CONCAT(title, ' by ', author) AS label
FROM books;
Output
+--------------------------+-------+------------+----------------+-------------------------------------------+
| title                    | price | sale_price | cents_per_page | label                                     |
+--------------------------+-------+------------+----------------+-------------------------------------------+
| Clean Code               | 34.99 |     31.491 |           7.54 | Clean Code by Robert Martin               |
| The Pragmatic Programmer | 42.50 |     38.250 |          12.07 | The Pragmatic Programmer by Hunt & Thomas |
| Dune                     | 12.99 |     11.691 |           1.89 | Dune by Frank Herbert                     |
| Project Hail Mary        | 16.00 |     14.400 |           3.23 | Project Hail Mary by Andy Weir            |
| Sapiens                  | 18.75 |     16.875 |           3.66 | Sapiens by Yuval Harari                   |
| The Martian              | 10.50 |      9.450 |           2.73 | The Martian by Andy Weir                  |
+--------------------------+-------+------------+----------------+-------------------------------------------+

Computed columns don't change the table. They exist only in the result.

Aliases#

AS renames a column (or a table) in the result. The AS keyword is optional, but including it reads better. Quote aliases that contain spaces with backticks or double quotes:

SQL
SELECT title AS `Book title`, published AS year FROM books AS b LIMIT 2;
Output
+--------------------------+------+
| Book title               | year |
+--------------------------+------+
| Clean Code               | 2008 |
| The Pragmatic Programmer | 2019 |
+--------------------------+------+

Table aliases (books AS b) become essential once you join tables, because they let you write b.title instead of books.title.

DISTINCT: removing duplicates#

SQL
SELECT DISTINCT genre FROM books;
Output
+-------------+
| genre       |
+-------------+
| programming |
| sci-fi      |
| history     |
+-------------+

With several columns, DISTINCT removes duplicate combinations:

SQL
SELECT DISTINCT author, genre FROM books WHERE genre = 'sci-fi';
Output
+---------------+--------+
| author        | genre  |
+---------------+--------+
| Frank Herbert | sci-fi |
| Andy Weir     | sci-fi |
+---------------+--------+

Andy Weir wrote two sci-fi books, but the pair (Andy Weir, sci-fi) appears only once.

LIMIT: taking only some rows#

SQL
SELECT title FROM books ORDER BY id LIMIT 3;          -- first 3 rows
SELECT title FROM books ORDER BY id LIMIT 2 OFFSET 3; -- skip 3, then take 2
Output
+--------------------------+
| title                    |
+--------------------------+
| Clean Code               |
| The Pragmatic Programmer |
| Dune                     |
+--------------------------+
+-------------------+
| title             |
+-------------------+
| Project Hail Mary |
| Sapiens           |
+-------------------+

LIMIT 3, 2 is an older shorthand for LIMIT 2 OFFSET 3 (offset first!). The explicit OFFSET form is easier to read.

Without ORDER BY, SQL doesn't promise any particular order. "The first 3 rows" can change between runs. Pair LIMIT with ORDER BY whenever the order matters. You'll learn sorting properly in the next lesson.

Selecting without a table#

SELECT can evaluate expressions on their own, which is perfect for trying out functions:

SQL
SELECT 7 / 2 AS division, 7 DIV 2 AS int_division, 7 % 2 AS remainder, UPPER('sql') AS shout;
Output
+----------+--------------+-----------+-------+
| division | int_division | remainder | shout |
+----------+--------------+-----------+-------+
|   3.5000 |            3 |         1 | SQL   |
+----------+--------------+-----------+-------+

Note that / always gives a decimal result in MySQL, while DIV gives an integer.

How MySQL reads your query#

You write clauses in this order:

SQL
SELECT DISTINCT genre, COUNT(*) AS n   -- 5. choose/compute columns
FROM books                             -- 1. pick the table(s)
WHERE price < 40                       -- 2. filter rows
GROUP BY genre                         -- 3. group rows
HAVING COUNT(*) >= 1                   -- 4. filter groups
ORDER BY n DESC                        -- 6. sort
LIMIT 10;                              -- 7. cut

But the database evaluates them roughly in the numbered order. This explains a classic surprise: you can't use a SELECT alias inside WHERE (step 2 happens before step 5), but you can use it in ORDER BY. Don't worry about GROUP BY and HAVING yet; they have their own lesson.

Formatting tips#

  • Put each major clause on its own line and indent column lists. Long queries become much easier to read and review.
  • End every statement with ;.
  • Comments start with -- (dash, dash, space) or go between /* ... */.

Common mistakes#

  • Commas. A missing comma between columns, as in SELECT title author FROM books, isn't an error: it silently makes author an alias for title! A trailing comma before FROM is an error.
  • Using a SELECT alias in WHERE. WHERE sale_price < 10 fails with Unknown column. Repeat the expression, or use a subquery or CTE (covered later).
  • Assuming row order. Without ORDER BY, it's undefined.

What's next#

Next you'll zoom in on exactly the rows you need with WHERE and its operators, and put them in order with ORDER BY and LIMIT.

Check your understanding

Quick quiz

0/3 answered

  1. 1.Which query returns each distinct genre only once?

  2. 2.What does SELECT * FROM books LIMIT 5 OFFSET 10; return?

  3. 3.Why should application code usually avoid SELECT *?

Finished reading?

Mark this lesson complete to track your progress.