MySQL SELECT Statement

Last Updated: 26 July, 2023

The SELECT statement in MySQL is used to retrieve data from one or more tables in a database. The data returned is stored in a result table, called the result-set.

The syntax of the SELECT statement is as follows:

  SELECT select_list
  FROM table_name
  [WHERE condition]
  [GROUP BY group_by_column]
  [ORDER BY order_by_column]
  [LIMIT number_of_rows]

• The SELECT keyword is followed by the select_list, which is a list of the columns that we want to select. The select_list can be a single column, multiple columns, or a combination of columns and expressions.

• The FROM keyword is followed by the name of the table that we want to select data from.

• The WHERE clause is used to filter the rows that are returned. The WHERE clause can contain a condition that is evaluated for each row. If the condition is true, the row is included in the result-set.

• The GROUP BY clause is used to group the rows that are returned. The GROUP BY clause can contain a single column or multiple columns. The rows that are grouped together are the rows that have the same value for the column(s) in the GROUP BY clause.

• The ORDER BY clause is used to order the rows that are returned. The ORDER BY clause can contain a single column or multiple columns. The rows are ordered in ascending order by default. To order the rows in descending order, we can use the DESC keyword.

• The LIMIT clause is used to limit the number of rows that are returned. The LIMIT clause takes two arguments: the first argument is the number of rows to return, and the second argument is the offset, which is the number of rows to skip.

Now, let's see the examples of MySQL SELECT statement.

Suppose we have a table called employees with the following structure:

Table: employees

  +----+-----------+------------+--------+------------+---------------+
  | ID | Name      | Department |   Age  |   Salary   |  JoiningDate  | 
  +----+-----------+------------+--------+------------+---------------+
  | 1  | Rajesh    | Sales      |   43   |    54,000  |   2023-06-11  |
  | 2  | Kamal     | HR         |   35   |    38,000  |   2021-01-23  |
  | 3  | Vimal     | Sales      |   52   |    68,000  |   2022-04-08  |
  | 4  | Hina      | Marketing  |   38   |    72,000  |   2018-08-16  |
  | 5  | Jyanti    | Finance    |   29   |    60,000  |   2010-02-24  |
  | 6  | Sanjay    | HR         |   31   |    55,000  |   2023-01-29  |
  | 7  | Amar      | Finance    |   27   |    40,000  |   2023-07-19  | 
  +----+-----------+------------+--------+------------+---------------+

Fetching records from all the columns

If we need to fetch the records from all the columns in a table, we use * marks instead of writing all the column names of the table.

Syntax:

  SELECT * FROM table_name;

Example:

  mysql> SELECT * FROM employees;
  +----+--------+------------+------+--------+-------------+
  | ID | Name   | Department | Age  | Salary | JoiningDate |
  +----+--------+------------+------+--------+-------------+
  |  1 | Rajesh | Sales      |   43 |  54000 | 2023-06-11  |
  |  2 | Kamal  | HR         |   35 |  38000 | 2021-01-23  |
  |  3 | Vimal  | Sales      |   52 |  68000 | 2022-04-08  |
  |  4 | Hina   | Marketing  |   38 |  72000 | 2018-08-16  |
  |  5 | Jyanti | Finance    |   29 |  60000 | 2010-02-24  |
  |  6 | Sanjay | HR         |   31 |  55000 | 2023-01-29  |
  |  7 | Amar   | Finance    |   27 |  40000 | 2023-07-19  |
  +----+--------+------------+------+--------+-------------+
  7 rows in set (0.03 sec)

Fetching records from certain columns

If we need to fetch the records from certain columns in a table, we write the column name of the table.

Syntax:

  SELECT column_1, column_2, column_n FROM table_name;

Example:

  mysql> SELECT ID, Name, Salary FROM employees;
  +----+--------+--------+
  | ID | Name   | Salary |
  +----+--------+--------+
  |  1 | Rajesh |  54000 |
  |  2 | Kamal  |  38000 |
  |  3 | Vimal  |  68000 |
  |  4 | Hina   |  72000 |
  |  5 | Jyanti |  60000 |
  |  6 | Sanjay |  55000 |
  |  7 | Amar   |  40000 |
  +----+--------+--------+
  7 rows in set (0.04 sec)

Fetching conditional records from a table

In MySQL, we can fetch conditional records from a table using the WHERE clause.

Syntax:

  SELECT * FROM table_name WHERE condition;

Example:

  mysql> SELECT * FROM employees WHERE Age > 40;
  +----+--------+------------+------+--------+-------------+
  | ID | Name   | Department | Age  | salary | JoiningDate |
  +----+--------+------------+------+--------+-------------+
  |  1 | Rajesh | Sales      |   43 |  54000 | 2023-06-11  |
  |  3 | Vimal  | Sales      |   52 |  68000 | 2022-04-08  |
  +----+--------+------------+------+--------+-------------+
  2 rows in set (0.00 sec)

Here, we are fetching conditional records, which means fetching only those records from the employees table whose age is more than 40.

Fetching the records in an order

In MySQL, we can fetch the records from a table in ascending or descending order using ORDER BY clause.

Syntax:

  SELECT column1, column2, ...
  FROM table_name
  ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...

Example:

  mysql> SELECT * FROM employees ORDER BY Salary DESC;
  +----+--------+------------+------+--------+-------------+
  | ID | Name   | Department | Age  | Salary | JoiningDate |
  +----+--------+------------+------+--------+-------------+
  |  4 | Hina   | Marketing  |   38 |  72000 | 2018-08-16  |
  |  3 | Vimal  | Sales      |   52 |  68000 | 2022-04-08  |
  |  5 | Jyanti | Finance    |   29 |  60000 | 2010-02-24  |
  |  6 | Sanjay | HR         |   31 |  55000 | 2023-01-29  |
  |  1 | Rajesh | Sales      |   43 |  54000 | 2023-06-11  |
  |  7 | Amar   | Finance    |   27 |  40000 | 2023-07-19  |
  |  2 | Kamal  | HR         |   35 |  38000 | 2021-01-23  |
  +----+--------+------------+------+--------+-------------+
  7 rows in set (0.00 sec)

We are sorting the records in descending order according to the Salary column.

Limit the number of returned rows

The LIMIT clause is used to limit the number of rows that are returned.

Syntax:

  SELECT column1, column2, ...
  FROM table_name
  [WHERE condition]
  [ORDER BY column1, column2, ...]
  LIMIT [offset,] row_count;

Example:

  mysql> SELECT * FROM employees LIMIT 4;
  +----+--------+------------+------+--------+-------------+
  | ID | Name   | Department | Age  | salary | JoiningDate |
  +----+--------+------------+------+--------+-------------+
  |  1 | Rajesh | Sales      |   43 |  54000 | 2023-06-11  |
  |  2 | Kamal  | HR         |   35 |  38000 | 2021-01-23  |
  |  3 | Vimal  | Sales      |   52 |  68000 | 2022-04-08  |
  |  4 | Hina   | Marketing  |   38 |  72000 | 2018-08-16  |
  +----+--------+------------+------+--------+-------------+
  4 rows in set (0.05 sec)

Fetching only first four records from a employees table.