Chapter 06 of 35

SELECT Statement

So far we have created the company_db database, built its tables and filled them with data using INSERT INTO. Data sitting in a table is useless until we can read it back, and that is exactly what the SELECT statement does.

SELECT is the most used command in SQL. Reports, dashboards, the "My Orders" page of a shopping app, and almost every interview question start with a SELECT. In this chapter we will learn its basic form: choosing columns, calculating new values, and renaming the result with aliases. Filtering rows (WHERE) and sorting (ORDER BY) get their own chapters next.

All examples use the shared company_db sample database, and every output shown is the real output from MySQL 8.4.


What is the SELECT Statement?

The SELECT statement retrieves data from one or more tables and returns it as a result set: a temporary table of rows and columns.

The simplest form is:

SELECT column1, column2, ...
FROM table_name;
  • SELECT says which columns you want.

  • FROM says which table they come from.

Two important things to understand from the start:

  • SELECT is read-only. It never changes the data stored in the table.

  • The result is itself a table. This is why you can later sort it, filter it, join it, or even use it inside another query (a subquery).


Selecting All Columns with *

The asterisk * means "all columns, in the order they were defined in the table".

SELECT * FROM departments;
+---------+-------------+-----------+
| dept_id | dept_name   | location  |
+---------+-------------+-----------+
|      10 | Engineering | Bengaluru |
|      20 | Sales       | Mumbai    |
|      30 | HR          | Delhi     |
|      40 | Marketing   | Pune      |
|      50 | Research    | Hyderabad |
+---------+-------------+-----------+
5 rows in set (0.00 sec)

The line 5 rows in set is printed by the MySQL command-line client. It is not part of the data; it just tells you how many rows came back.

Why You Should Avoid SELECT * in Real Code

SELECT * is perfect for quickly exploring a table. In application code and reports, however, it is considered a bad habit:

  • Wasted work: the database reads and sends every column over the network, even ones you never use (imagine a table with a large description or image column).

  • Fragile code: if someone adds, removes or reorders a column, the shape of your result changes silently, and code that reads "the third column" breaks.

  • Unclear intent: a reader cannot tell which columns the query actually needs.

  • Lost optimisations: when you ask only for indexed columns, MySQL can sometimes answer from the index alone without touching the table. SELECT * usually rules that out. (We will see this in the Indexes chapter.)

Rule of thumb: use SELECT * to explore, but name your columns in anything you save or ship.


Selecting Specific Columns

List the columns you want, separated by commas:

SELECT name, salary FROM employees;
+--------------+-----------+
| name         | salary    |
+--------------+-----------+
| Aarav Sharma | 150000.00 |
| Priya Iyer   |  95000.00 |
| Rohan Mehta  |  95000.00 |
| Sneha Kapoor | 110000.00 |
| Vikram Singh |  62000.00 |
| Ananya Das   |  58000.00 |
| Karan Patel  |  70000.00 |
| Meera Nair   |  48000.00 |
| Arjun Reddy  |  82000.00 |
| Ishita Gupta |  55000.00 |
+--------------+-----------+
10 rows in set (0.00 sec)

The employees table has 8 columns, but we asked for only 2, so we got only 2. This operation of picking columns is called projection in relational algebra (the symbol π you may have seen in the DBMS tutorial).

Column Order is Up to You

The columns appear in the order you write them, not the order they are stored in:

SELECT salary, name FROM employees;
+-----------+--------------+
| salary    | name         |
+-----------+--------------+
| 150000.00 | Aarav Sharma |
|  95000.00 | Priya Iyer   |
|  95000.00 | Rohan Mehta  |
| 110000.00 | Sneha Kapoor |
|  62000.00 | Vikram Singh |
|  58000.00 | Ananya Das   |
|  70000.00 | Karan Patel  |
|  48000.00 | Meera Nair   |
|  82000.00 | Arjun Reddy  |
|  55000.00 | Ishita Gupta |
+-----------+--------------+
10 rows in set (0.00 sec)

Notice that the rows came back in the same order as before. That is a coincidence of how MySQL happened to read the table. SQL does not promise any row order unless you ask for one with ORDER BY (covered in the ORDER BY & LIMIT chapter).


Calculated Columns (Expressions)

A SELECT list is not limited to plain column names. Each item can be any expression: arithmetic, a function call, a constant, or a mix of these. The expression is evaluated once for every row.

SELECT name, salary, salary * 12 FROM employees;
+--------------+-----------+-------------+
| name         | salary    | salary * 12 |
+--------------+-----------+-------------+
| Aarav Sharma | 150000.00 |  1800000.00 |
| Priya Iyer   |  95000.00 |  1140000.00 |
| Rohan Mehta  |  95000.00 |  1140000.00 |
| Sneha Kapoor | 110000.00 |  1320000.00 |
| Vikram Singh |  62000.00 |   744000.00 |
| Ananya Das   |  58000.00 |   696000.00 |
| Karan Patel  |  70000.00 |   840000.00 |
| Meera Nair   |  48000.00 |   576000.00 |
| Arjun Reddy  |  82000.00 |   984000.00 |
| Ishita Gupta |  55000.00 |   660000.00 |
+--------------+-----------+-------------+
10 rows in set (0.00 sec)

Here is what happened for the first row, step by step:

Row 1:  name = 'Aarav Sharma', salary = 150000.00
        salary * 12  →  150000.00 * 12  →  1800000.00
Output: Aarav Sharma | 150000.00 | 1800000.00

The stored salary is not changed. The value 1800000.00 exists only in the result set.

Arithmetic Operators

Operator

Meaning

Example

Result

+

Addition

2 + 3

5

-

Subtraction

10 - 4

6

*

Multiplication

salary * 12

annual salary

/

Division (always gives a decimal)

10 / 4

2.5000

DIV

Integer division (MySQL)

10 DIV 4

2

% or MOD

Remainder

10 % 4

2

SELECT 10 / 4, 10 DIV 4, 10 % 4;
+--------+----------+--------+
| 10 / 4 | 10 DIV 4 | 10 % 4 |
+--------+----------+--------+
| 2.5000 |        2 |      2 |
+--------+----------+--------+
1 row in set (0.00 sec)

Note that in MySQL 10 / 4 is 2.5000, not 2. This surprises people coming from C or Java, where integer division drops the fraction. Some other databases (SQL Server, PostgreSQL) do return 2 for integer / integer, so always check which database you are on.

A Practical Example: Adding GST

Suppose every order amount must be shown with 18% GST:

SELECT order_id, amount, amount * 0.18 AS gst, amount * 1.18 AS total
FROM orders;
+----------+---------+-----------+-----------+
| order_id | amount  | gst       | total     |
+----------+---------+-----------+-----------+
|     1001 | 2500.00 |  450.0000 | 2950.0000 |
|     1002 | 4800.00 |  864.0000 | 5664.0000 |
|     1003 | 1200.00 |  216.0000 | 1416.0000 |
|     1004 | 7300.00 | 1314.0000 | 8614.0000 |
|     1005 |  950.00 |  171.0000 | 1121.0000 |
|     1006 | 3100.00 |  558.0000 | 3658.0000 |
|     1007 | 5600.00 | 1008.0000 | 6608.0000 |
|     1008 |  800.00 |  144.0000 |  944.0000 |
+----------+---------+-----------+-----------+
8 rows in set (0.00 sec)

Why four decimal places? amount is DECIMAL(10,2) and 0.18 has 2 decimal places. When MySQL multiplies two exact decimals, the result keeps the sum of their scales (2 + 2 = 4). If you want two places, wrap it in ROUND(amount * 0.18, 2), which rounds to 2 decimal places; you will see ROUND() used often from the Aggregate Functions chapter onward.

Using Functions in SELECT

Built-in functions also work row by row. CONCAT() joins strings:

SELECT CONCAT(name, ' (', city, ')') AS name_with_city FROM employees;
+--------------------------+
| name_with_city           |
+--------------------------+
| Aarav Sharma (Bengaluru) |
| Priya Iyer (Chennai)     |
| Rohan Mehta (Bengaluru)  |
| Sneha Kapoor (Mumbai)    |
| Vikram Singh (Mumbai)    |
| Ananya Das (Kolkata)     |
| Karan Patel (Delhi)      |
| Meera Nair (Kochi)       |
| Arjun Reddy (Hyderabad)  |
| NULL                     |
+--------------------------+
10 rows in set (0.00 sec)

Look at the last row. Ishita Gupta's city is NULL (unknown), and CONCAT with any NULL argument returns NULL, so even her name disappeared. This "NULL swallows everything" behaviour is very important, and we will study it properly in the NULL Values chapter.

Constant Columns

You can also select a fixed value. It is repeated on every row:

SELECT order_id, customer_id, 'Online' AS channel FROM orders;
+----------+-------------+---------+
| order_id | customer_id | channel |
+----------+-------------+---------+
|     1008 |        NULL | Online  |
|     1001 |         101 | Online  |
|     1003 |         101 | Online  |
|     1007 |         101 | Online  |
|     1002 |         102 | Online  |
|     1006 |         102 | Online  |
|     1004 |         103 | Online  |
|     1005 |         104 | Online  |
+----------+-------------+---------+
8 rows in set (0.00 sec)

Here the rows came back grouped by customer_id, not by order_id. Nothing is wrong: because we asked only for columns that exist in the index on customer_id (plus the primary key, which InnoDB stores in every index), MySQL read that index instead of the table, and the index is sorted by customer. This is the clearest proof that without ORDER BY, row order is not guaranteed.


SELECT Without a Table

In MySQL, FROM is optional when you do not need any table. This turns SQL into a calculator and is handy for testing functions:

SELECT 2 + 3 AS result, NOW() IS NOT NULL AS has_time, UPPER('sql') AS shout;
+--------+----------+-------+
| result | has_time | shout |
+--------+----------+-------+
|      5 |        1 | SQL   |
+--------+----------+-------+
1 row in set (0.00 sec)

MySQL shows true as 1 and false as 0. (Oracle traditionally needed FROM dual for this, until Oracle 23ai made FROM optional; MySQL accepts FROM dual too, but does not require it.)


Column Aliases (AS)

The heading salary * 12 is ugly, and code that reads the result by column name would have to use that exact text. An alias gives a result column a new name:

SELECT name, salary * 12 AS annual_salary FROM employees;
+--------------+---------------+
| name         | annual_salary |
+--------------+---------------+
| Aarav Sharma |    1800000.00 |
| Priya Iyer   |    1140000.00 |
| Rohan Mehta  |    1140000.00 |
| Sneha Kapoor |    1320000.00 |
| Vikram Singh |     744000.00 |
| Ananya Das   |     696000.00 |
| Karan Patel  |     840000.00 |
| Meera Nair   |     576000.00 |
| Arjun Reddy  |     984000.00 |
| Ishita Gupta |     660000.00 |
+--------------+---------------+
10 rows in set (0.00 sec)

An alias changes only the heading in the result. The table's real column is still called salary.

Aliases with Spaces

If the alias contains spaces or is a reserved word, quote it. In MySQL you can use backticks or quotes:

SELECT name AS `Employee Name`, salary AS 'Monthly Salary' FROM employees;
+---------------+----------------+
| Employee Name | Monthly Salary |
+---------------+----------------+
| Aarav Sharma  |      150000.00 |
| Priya Iyer    |       95000.00 |
| Rohan Mehta   |       95000.00 |
| Sneha Kapoor  |      110000.00 |
| Vikram Singh  |       62000.00 |
| Ananya Das    |       58000.00 |
| Karan Patel   |       70000.00 |
| Meera Nair    |       48000.00 |
| Arjun Reddy   |       82000.00 |
| Ishita Gupta  |       55000.00 |
+---------------+----------------+
10 rows in set (0.00 sec)

The standard SQL way to quote an identifier is double quotes ("Employee Name"). MySQL treats double quotes as a string by default, which happens to work for aliases too, but backticks are the safest choice in MySQL. Prefer simple snake_case aliases and avoid quoting entirely.

AS is Optional (and That Can Bite You)

The keyword AS can be left out:

SELECT name employee, city FROM employees;
+--------------+-----------+
| employee     | city      |
+--------------+-----------+
| Aarav Sharma | Bengaluru |
| Priya Iyer   | Chennai   |
| Rohan Mehta  | Bengaluru |
| Sneha Kapoor | Mumbai    |
| Vikram Singh | Mumbai    |
| Ananya Das   | Kolkata   |
| Karan Patel  | Delhi     |
| Meera Nair   | Kochi     |
| Arjun Reddy  | Hyderabad |
| Ishita Gupta | NULL      |
+--------------+-----------+
10 rows in set (0.00 sec)

Because AS is optional, a missing comma does not cause an error. Look at this typo:

SELECT name salary FROM employees;   -- meant: name, salary
+--------------+
| salary       |
+--------------+
| Aarav Sharma |
| Priya Iyer   |
| Rohan Mehta  |
| Sneha Kapoor |
| Vikram Singh |
| Ananya Das   |
| Karan Patel  |
| Meera Nair   |
| Arjun Reddy  |
| Ishita Gupta |
+--------------+
10 rows in set (0.00 sec)

MySQL read it as "name with the alias salary". We got names under a column called salary, and no error at all. This is why many teams always write AS explicitly: it makes such mistakes easy to spot.


Table Aliases and Qualified Column Names

You can write a column as table.column. This is called a qualified name:

SELECT employees.name, employees.dept_id FROM employees;
+--------------+---------+
| name         | dept_id |
+--------------+---------+
| Aarav Sharma |      10 |
| Priya Iyer   |      10 |
| Rohan Mehta  |      10 |
| Sneha Kapoor |      20 |
| Vikram Singh |      20 |
| Ananya Das   |      20 |
| Karan Patel  |      30 |
| Meera Nair   |      30 |
| Arjun Reddy  |      40 |
| Ishita Gupta |    NULL |
+--------------+---------+
10 rows in set (0.00 sec)

Typing the full table name gets tiring, so we give the table a short table alias:

SELECT e.name, e.salary FROM employees AS e;

The result is the same name and salary list we saw earlier. With only one table this looks pointless, but it becomes essential in the SQL Joins chapters: both employees and departments have a dept_id column, and e.dept_id vs d.dept_id tells MySQL exactly which one you mean.


Checking a Table's Columns First

Before writing a SELECT on an unfamiliar table, look at its structure:

DESCRIBE employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_id     | int           | NO   | PRI | NULL    |       |
| name       | varchar(50)   | NO   |     | NULL    |       |
| email      | varchar(100)  | YES  | UNI | NULL    |       |
| dept_id    | int           | YES  | MUL | NULL    |       |
| manager_id | int           | YES  | MUL | NULL    |       |
| salary     | decimal(10,2) | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
| city       | varchar(50)   | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

This tells you the exact column names (so you do not guess emp_name when it is name), their types, and which ones may hold NULL.


Common Errors

Spelling mistakes in table or column names produce clear errors:

SELECT name FROM employee;
ERROR 1146 (42S02): Table 'company_db.employee' doesn't exist
SELECT nmae FROM employees;
ERROR 1054 (42S22): Unknown column 'nmae' in 'field list'

"field list" is MySQL's name for the list of items after SELECT. When you see error 1054, check the spelling and then check whether the column really belongs to that table (DESCRIBE helps).


How MySQL Processes a SELECT

We write SELECT first, but the database does not process it first. The logical order in which the clauses are evaluated is:

Written order              Logical processing order
-------------              ------------------------
SELECT                     1. FROM      (which table?)
FROM                       2. WHERE     (which rows?)
WHERE                      3. GROUP BY  (make groups)
GROUP BY                   4. HAVING    (which groups?)
HAVING                     5. SELECT    (compute columns, aliases)
ORDER BY                   6. DISTINCT  (remove duplicates)
LIMIT                      7. ORDER BY  (sort)
                           8. LIMIT     (cut the result)

You will meet each clause in the coming chapters. For now, remember two consequences, because they explain many errors:

  • A column alias is created in step 5, so WHERE (step 2) cannot use it. You will see this error in the WHERE Clause chapter.

  • ORDER BY runs after SELECT, so ORDER BY can use an alias.

This is the logical order. The optimizer may physically do things differently (for example, use an index), but the result is always as if it followed these steps.


Style Tips

  • SQL keywords are not case-sensitive: select, SELECT and Select all work. Writing keywords in UPPERCASE and names in lowercase is the common convention because it is easy to read.

  • Whitespace and line breaks do not matter. Put each major clause on its own line once queries grow.

  • End each statement with a semicolon ;. The mysql client waits for it before running the query.

  • Table names may be case-sensitive depending on the operating system (on Linux, Employees and employees are different tables by default). Always use the exact case.

SELECT name,
       salary * 12 AS annual_salary
FROM   employees;

Common Mistakes

Mistake

What happens

Fix

Missing comma between columns

Second name silently becomes an alias

Always write AS for aliases so typos stand out

Trailing comma: SELECT name, salary, FROM ...

Syntax error 1064

Remove the last comma

Expecting rows in insertion order

Order may change between runs or after an index is added

Use ORDER BY

Using SELECT * in application code

Extra I/O, breaks when columns change

List only needed columns

Assuming / does integer division

7 / 2 gives 3.5000 in MySQL

Use DIV for integer division

Forgetting NULL in expressions

CONCAT or arithmetic with NULL gives NULL

Handle it with IFNULL/COALESCE (NULL Values chapter)


Interview Questions

Does SELECT modify the data in a table?

No. SELECT only reads data and returns a result set. Computed columns such as salary * 12 exist only in the result. (The exception is SELECT ... FOR UPDATE, which still does not change data but locks rows; that is a transactions topic.)

What is the difference between a column alias and a table alias?

A column alias renames a column in the result (salary * 12 AS annual_salary). A table alias gives a table a short name inside the query (FROM employees AS e) so columns can be qualified as e.name; it is essential in joins and self-joins.

Why can't you use a column alias in the WHERE clause?

Because logically WHERE is evaluated before the SELECT list, so the alias does not exist yet. ORDER BY runs after SELECT, so it can use aliases.

What is the logical order of execution of a SELECT query?

FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.

Is the order of rows in a SELECT result guaranteed?

No. Without ORDER BY the database returns rows in whatever order is cheapest for it, which can depend on indexes and the query plan. Only ORDER BY guarantees order.

Why is SELECT * discouraged in production queries?

It fetches unnecessary columns (more I/O and network traffic), prevents index-only (covering) reads, and makes code depend on the table's current column list, which may change.

Which relational algebra operation does choosing columns in SELECT correspond to?

Projection (π). Choosing rows with WHERE corresponds to selection (σ), which is confusingly named, since SQL's SELECT keyword actually does projection.


Summary

  • SELECT columns FROM table reads data and returns a result set; it never changes the table.

  • * returns all columns. Good for exploring, avoid it in real code.

  • The SELECT list can contain expressions, functions and constants, evaluated once per row.

  • In MySQL, / gives a decimal result; use DIV for integer division.

  • AS creates column aliases and table aliases. AS is optional, which can hide a missing comma.

  • Any expression involving NULL usually becomes NULL.

  • Logical order: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.

  • Row order is never guaranteed without ORDER BY.

Next, in the WHERE Clause chapter, we will learn how to return only the rows we need instead of the whole table.