SELECT Statement in MySQL

Back to: MySQL Tutorials for Beginners and Professionals

SELECT Statement in MySQL with Examples

In this article, I am going to discuss the SELECT Statement in MySQL with Examples. Please read our previous article where we discussed, how to Export and Import MySQL Database using MySQL Workbench.

SELECT Statement in MySQL

Now we will learn about the SELECT clause which is the most important clause in the structured query language. The SELECT clause is the first clause in any SELECT statement. The SELECT statement’s main purpose is to retrieve the data from the database table and return it in a tabular structure. The SELECT clause defines the columns that will be returned in the final tabular result set.

The SELECT clause is executed after the FROM clause and any optional WHERE, GROUP BY, and HAVING clauses if present. The FROM clause builds an intermediate tabular result set from which the SELECT clause ultimately selects the data to be returned. However, the presence of a GROUP BY clause changes the structure of this intermediate table, thus changing the data available to the SELECT clause when it’s finally executed.

What is the Use of Select Statement in MySQL?

The SELECT Statement is used to returns records in the form of a result set (i.e. rows) from one or more database tables or views. The most important point that you need to remember is, the SELECT statement does not store any data itself. It simply displays the data that is stored in database tables. The Select Statement can retrieve and shows the data from one or more database tables, from other queries, or from a combination of the above two.

Select Statement Syntax in MySQL:

Syntax1: SELECT All Columns
If you want to select all the columns of a table or view then you can also use “*” as shown below
SELECT * FROM Table_Name
Note: In the SQL SELECT statement (*) star or asterisk symbol is a special type of keyword that means ALL. Whenever you want to retrieve all columns from a table or view in a SQL SELECT Statement, then you can use *.

Syntax2: SELECT Specific Columns
If you want to select specific columns, then you need to specify such columns before the FROM Clause as shown below.
SELECT Column_List FROM Table_Name
Note: If you want to select all the columns of a table or view then you can also use “*”, but for better performance use the column list instead of using “*”.

Syntax3: SELECT All Columns with Conditions
If you want to select All Columns with WHERE (or any other) conditions then you need to use the select statement as shown below
SELECT * FROM tables [WHERE conditions];

Syntax4: SELECT Specified Columns with Conditions
If you want to select Specified Columns with WHERE (or any other) conditions then you need to use the select statement as shown below. Here, you need to specify the column list that you want to retrieve.
SELECT Column_List FROM tables [WHERE conditions];

Examples to Understand SELECT Statement in MySQL:

We are going to use the following employee table to understand the need and use of MySQL SELECT Statement with Examples.

Please use the below SQL Script to create the database company and employees table with the required data.

CREATE DATABASE company;
USE company;

CREATE TABLE employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(45) NOT NULL,
  Department VARCHAR(45) NOT NULL,
  Salary FLOAT NOT NULL,
  Gender VARCHAR(45) NOT NULL,
  Age INT NOT NULL,
  City VARCHAR(45) NOT NULL
);

INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1001, 'John Doe', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1002, 'Mary Smith', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1003, 'James Brown', 'Finance', 50000, 'Male', 28, 'Delhi');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike Walker', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda Jones', 'HR', 75000, 'Female', 26, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag Mohanty', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanla Dewangan', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit Mohanty', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya Kumar', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina Sharma', 'HR', 75000, 'Female', 26, 'Mumbai');

Once you execute the above SQL statements, then it will create the company database and inside it will create the employee table as shown in the below image.

Example: Retrieve all fields from the Employee table

Let’s see how to write a SELECT statement to retrieve all columns from the Employee table. As we are going to retrieve all columns so we have two options.

Option1: Use * as follows
SELECT * FROM employee;

Option2: Specify all the Columns in SELECT Class as follows:
SELECT Id, Name, Department, Salary, Gender, Age, City FROM employee;

In both cases, when you execute the query, you will get the same result as shown in the below image.

Example: Retrieve Specific Columns from Employee table

Let’s see how to write a SELECT statement to retrieve Name, Department, Gender, and City columns from the Employee table. As we are going to retrieve specific columns so we can’t use *, instead here we need to specify the required columns that we want to retrieve in the SELECT clause as shown in the below example.

SELECT Name, Department, Gender, City FROM employee;

When you execute the above query, you will get the following results which include only the Name, Department, Gender, and City columns in the result set.

Example: Retrieve all Columns with Condition in MySQL

Now, let’s see how to write a SELECT SQL statement to retrieve all columns from the Employee table with some conditions. The condition is we need to retrieve those employees whose Department is IT. Again, as we are going to retrieve all columns so we have two options. Here, we are using the WHERE Clause to provide the condition. In our next article, we are going to discuss the need and use of the MySQL WHERE clause in detail.

Option1: Use * as follows
SELECT * FROM employee WHERE Department = ‘IT’;

Option2: Specify all the Columns in SELECT Class as follows:
SELECT Id, Name, Department, Salary, Gender, Age, City FROM employee;

In both cases, when you execute the query, you will get the same result as shown in the below image.

FROM Clause in MySQL

As of now we are using the FROM Clause in the select statement and specifying the table name from where we want to retrieve the data. Let us learn something about the FROM Clause.

The from clause can be simple, and it can also be quite complex. The FROM clause forms a tabular structure. This tabular structure is referred to as the “result set” or an “intermediate result set” or an “intermediate table” of the FROM clause.

If we get the FROM clause wrong, the SQL statement will always return the wrong results. It’s the FROM clause that produces the tabular structure, the starting set of data on which all other operations in a SELECT statement are performed.

The FROM clause is the first clause that the database system looks at when it parses the SQL statement. The FROM statements can return the result sets from one table or more than one table using joins, views, and subqueries. We will learn more about joins, views, and subqueries in a separate dedicated section. Let’s learn about FROM clause with some examples. Please have a look at the following SQL Statement.

SELECT * FROM employee;

When we execute the above SQL statement, it returns all the columns and all the rows from the employee table as shown in the below image. What basically the FROM clause does is, it will try to get all the columns from the table employee which is specified by the FROM clause.

Now, let see what happens when we try to get a result set from a table that does not exist in the current database as shown below. Here, the table employee1 does not exist in the company database.

SELECT * FROM employee1;

When we try to execute the above SQL Query, we will get an error that is Error Code: 1146. Table ‘company.employee1’ doesn’t exist.

Here in this SQL statement, the system first processes the “FROM clause” and checks if the table name employee1 exists or is valid. Even if the employee1 table exists in another database, as we have selected and working on the “company” database here, the system did not execute the SQL statement. This SQL statement proves that FROM clause is the first clause that the database system looks at when it parses the SQL statement.

SELECT DISTINCT Statement in MySQL

The SELECT DISTINCT SQL statement in MySQL is used to return only distinct or different values from a table column. In a database table, a column may contain duplicate or similar values. For example: In our employee database table, in Department and Gender column we have duplicate values.

That means multiple employees can be in the same Department or have the same salary. In such a case, if we want to get only the distinct values from the columns, the SELECT DISTINCT Statement is very useful. The SELECT DISTINCT Statement in MySQL will return all the distinct values in the given column.

Let’s see the SELECT DISTINCT SQL statement with some examples. My requirement is to fetch all the distinct departments from the employee table. The following SQL query exactly does the same.

SELECT distinct Department FROM employee;

When you execute the above SQL Query, you will get the following result.

We can also combine 2 or multiple columns to get distinct values. In the below SQL Statement, we are combining Department and Salary column.

SELECT distinct Department, Salary FROM employee;

When we execute the above query, we will get the below output.

Note: There are many things that we can do with the SELECT SQL Statement that we are going to discuss in our upcoming articles.

In the next article, I am going to discuss How to use the WHERE Clause in MySQL with Examples. Here, in this article, I try to give an overview of the SELECT SQL Statement in MySQL with Examples. I hope you enjoy this SELECT Statement in MySQL with Examples article.