Select Query To Fetch Rows in MySQL

This tutorial is part of the series Learn Basic SQL Queries Using MySQL. In this tutorial, we will discuss SQL queries to fetch the rows or read data from a table in MySQL.

Select Query

The command SELECT can be used to read rows from a table. In case you are remotely logged in to the database, you will also need the SELECT privilege for the table in order to read the rows from a table.

# SELECT - Syntax
SELECT [ ALL | DISTINCT | DISTINCTROW ] <select expression> FROM table_name;

# SELECT - Syntax - All Columns
SELECT [ ALL | DISTINCT | DISTINCTROW ] * FROM table_name;

# SELECT - Syntax - Selective Columns
SELECT [ ALL | DISTINCT | DISTINCTROW ] column_1,column_2,... FROM table_name;

# SELECT - Syntax - Filter results using WHERE
SELECT [ ALL | DISTINCT | DISTINCTROW ] column_1,column_2,... FROM table_name WHERE <single or multiple filter conditions>;

Query Explanation

The MySQL command SELECT can be used to read rows or data from the given table name where the select expression and name of the table is mandatory.

Using the keywords ALL or DISTINCT or DISTINCTROW is optional. The ALL is the default keyword in case none of them is specified.

We also need to specify the select expression in order to get data from the given table. We can either use * or the comma-separated column names to get the query results from the table. We can also specify an alias for the column names using the keyword AS.

The next tutorials of this series explain the advanced usage of the select query to filter, sort, or group the results using WHERE, LIKE, ORDER BY, and GROUP BY clauses.

Examples

This section provides examples to read data from the table using the SELECT command. Use the below-mentioned query to create the user table having the id, first name, and last name columns to store user data.

# Create the User Table
CREATE TABLE `user` (
  `user_id` bigint(20) NOT NULL,
  `first_name` varchar(45),
  `last_name` varchar(45)
);

The below-mentioned query can be used to insert data in the user table.

# Insert Rows - All Columns
INSERT INTO `user` ( `user_id`, `first_name`, `last_name`) VALUES 
    ( 1, 'John', 'Smith' ),
    ( 2, 'Rick', 'Jones' ),
    ( 3, 'Catherine', 'Ponting' ),
    ( 4, 'Harsh', 'Upadhyay' ),
    ( 5, 'Tajwinder', 'Singh' );

The above-mentioned query will insert 5 rows in the table having the id, first name and last name to represent 5 different users.

Now we will read the data inserted by us in the user table. It can be done using the SELECT command as shown below.

# Read all the columns and rows
SELECT * FROM `user`;

# Result
1	John		Smith
2	Rick		Jones
3	Catherine	Ponting
4	Harsh		Upadhyay
5	Tajwinder	Singh

# Read selective columns
SELECT `first_name`, `last_name` FROM `user`;

# Result
John		Smith
Rick		Jones
Catherine	Ponting
Harsh		Upadhyay
Tajwinder	Singh

The first query will show all the columns in the query results whereas the second query will show only the row data for the columns provided by us.

In case the table row count is bigger than 50 or 100, it's preferred to read the limited data either using LIMIT or OFFSET.

# Read limited rows
SELECT * FROM `user` LIMIT 2;

# Result
1	John	Smith
2	Rick	Jones

# Read limited rows using offset
SELECT * FROM `user` LIMIT 3, 2;

# Result
4	Harsh		Upadhyay
5	Tajwinder	Singh

We can use the keyword LIMIT to fetch limited rows by specifying the offset and limit values. In the above example, the first query will read the first two rows. The second query will also read 2 rows starting from the 4th row by specifying the offset value to 3.

This is how we can read the data stored in the MySQL tables.