SELECT

SELECT is the statement you will write more than any other. It reads data from one or more tables and gives you back a result, which is itself a small table of rows and columns. It never changes the stored data, so you can run it as often as you like.

In this lesson you will choose which columns to show, use * to show all of them, calculate new values on the fly and learn the order of the parts of a full query.

Choosing columns

The simplest query names some columns and one table:

SELECT column1, column2 FROM table_name;

After SELECT you list the columns you want, separated by commas. After FROM you name the table they come from. The result shows the columns in the order you listed them, not the order they have in the table.

SELECT product_name, category, price
FROM products;

product_name                | category    | price
----------------------------+-------------+------
Wireless Mouse              | Accessories | 799
Mechanical Keyboard         | Accessories | 3499
27-inch Monitor             | Displays    | 15999
USB-C Hub                   | Accessories | 1899
Laptop Stand                | Accessories | 1299
Noise-Cancelling Headphones | Audio       | 8999
Bluetooth Speaker           | Audio       | 2499
HD Webcam                   | Accessories | 2199
1TB External SSD            | Storage     | 6499
64GB Pen Drive              | Storage     | 549
Python Basics (Book)        | Books       | 450
Graphics Tablet             | Accessories | 4599
(12 rows)

Each row of the result is one product. The column headings are the column names you asked for. Try swapping price and product_name in the editor and run it again: the columns swap places too.

Selecting every column with *

The star * is a shortcut that means "all columns, in table order".

SELECT * FROM employees;

employee_id | first_name | last_name | department  | job_title           | salary | manager_id | hire_date
------------+------------+-----------+-------------+---------------------+--------+------------+-----------
1           | Rajesh     | Malhotra  | Management  | Chief Executive     | 250000 | NULL       | 2018-02-01
2           | Neha       | Bansal    | Engineering | Engineering Manager | 180000 | 1          | 2019-04-15
3           | Amit       | Chauhan   | Engineering | Senior Developer    | 120000 | 2          | 2020-07-01
4           | Pooja      | Saini     | Engineering | Developer           | 75000  | 2          | 2022-01-10
5           | Karan      | Dahiya    | Engineering | Developer           | 70000  | 2          | 2023-06-19
6           | Simran     | Kaur      | Sales       | Sales Manager       | 110000 | 1          | 2019-09-09
7           | Rahul      | Jangra    | Sales       | Sales Executive     | 45000  | 6          | 2021-11-22
8           | Nisha      | Rathi     | Sales       | Sales Executive     | 42000  | 6          | 2024-03-04
9           | Deepak     | Hooda     | Support     | Support Lead        | 65000  | 1          | 2020-12-14
10          | Tanvi      | Malik     | Support     | Support Executive   | 38000  | 9          | 2025-02-17
(10 rows)

SELECT * is perfect when you are exploring a table you have not seen before. In real application code, list the columns by name instead. There are three good reasons:

• Your program gets only the data it needs, which is faster, especially over a network.
• If someone adds a large column to the table later, your page does not suddenly fetch it.
• The code tells the next reader exactly which columns it uses.

Calculating new columns

A column in a SELECT list does not have to be a stored column. It can be an expression: a calculation that MySQL works out for every row. The arithmetic operators are +, -, * and /.

The store wants to know how much money is sitting in the warehouse for each product. That is the price multiplied by the stock:

SELECT product_name, price, stock, price * stock
FROM products;

product_name                | price | stock | price * stock
----------------------------+-------+-------+--------------
Wireless Mouse              | 799   | 120   | 95880
Mechanical Keyboard         | 3499  | 45    | 157455
27-inch Monitor             | 15999 | 18    | 287982
USB-C Hub                   | 1899  | 60    | 113940
Laptop Stand                | 1299  | 75    | 97425
Noise-Cancelling Headphones | 8999  | 25    | 224975
Bluetooth Speaker           | 2499  | 40    | 99960
HD Webcam                   | 2199  | 0     | 0
1TB External SSD            | 6499  | 30    | 194970
64GB Pen Drive              | 549   | 200   | 109800
Python Basics (Book)        | 450   | 80    | 36000
Graphics Tablet             | 4599  | 12    | 55188
(12 rows)

The 27-inch Monitor ties up the most money, ₹2,87,982, even though the pen drive has far more units in stock. The new column has no real name, so MySQL uses the expression text, price * stock, as its heading. In Aliases you will learn to give it a proper name such as stock_value.

SELECT without a table

You can even run SELECT without FROM. MySQL then works like a calculator, which is handy for checking a sum quickly:

SELECT 799 * 3, 15999 + 2199;

799 * 3 | 15999 + 2199
--------+-------------
2397    | 18198
(1 row)

Three wireless mice cost ₹2,397, and a monitor plus a webcam cost ₹18,198. The result has exactly one row because there is no table to read rows from.

The parts of a full query

A SELECT statement can have many more parts. You will learn each one in its own lesson, but they always appear in this order:

If you write them in a different order, for example ORDER BY before WHERE, MySQL stops with a syntax error.

Common mistakes

A comma after the last column. SELECT first_name, city, FROM customers; fails with a syntax error, because MySQL expects another column after the comma. Remove the extra comma.

A missing comma between columns. This one is sneaky, because it does not cause an error:

SELECT first_name city FROM customers;

city
------
Aarav
Priya
Rohan
Ananya
Kabir
Meera
Vikram
Sneha
Arjun
Isha
Farhan
Divya
(12 rows)

Without the comma, SQL reads city as a new name (an alias) for first_name. You get one column called city that is full of first names. If a result has fewer columns than you expected, look for a missing comma.

A misspelt column name. SELECT frist_name FROM customers; stops with ERROR 1054 (42S22): Unknown column 'frist_name' in 'field list'. Read the error: it names the exact word MySQL did not recognise.

Key points

• SELECT reads data and never changes it.
• List the columns you want after SELECT, separated by commas, and the table after FROM.
• * means every column; use it for exploring, and name columns in application code.
• A select list can hold calculations such as price * stock, worked out for every row.
• A missing comma silently turns the second column name into an alias for the first.
• Clauses always come in the order SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.

SELECT first_name, last_name, job_title, salary, salary * 12
FROM employees;