SQL SELECT STATEMENT

The SQL SELECT statement is used to fetch records from one or more SQL tables or views in your database. The records fetched are known as resultset which is shown in tabular format.

SQL or Structured Query Language is used for storing, retrieving, and manipulating data in the database. One of the most important aspects of SQL is to retrieve data from the database. SQL has different commands or statements to deals with these different aspects. SQL select is used to retrieve data from the tables or views.

Syntax

The basic syntax of SQL SELECT is as follows.

Select expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ASC | DESC]];

In this Syntax,

• expressions – expressions defined here the column(s) or calculation you want to retrieve. If you want to retrieve all the columns simply use *.
• tables – one or more than one table from where you want to retrieve data.
• WHERE conditions – Optional. This is used to specify some conditions while selecting data. In case you are not using WHERE clause all the rows available will be selected.
• ORDER BY – Optional. Sometimes we want to see the resultset in sorted manner. This argument is used to sort the resultset. If you want to sort on more than one column, you need to provide them in comma-separated.
• ASC – Optional. ASC sort the resultset in ascending order. This is the default behaviour if no modifier is mentioned.
• DESC – Optional. DESC sorts resultset in descending order by expression.

Example – Select All the Column form a Table

Suppose, we have an employee table with the following data.

Now, let’s check how the SELECT statement works by selecting all columns from the employee table.

SELECT * FROM employee
WHERE salary>70000
ORDER BY first_name ASC;

There will be 2 rows selected. Below the result, you should see.

mysql> select * from employee;
+-------+---------+-----------+--------+
| empno | name    | city      | salary |
+-------+---------+-----------+--------+
|     1 | Sagar   | Kolkata   |  85000 |
|     2 | Shankar | New Delhi |  80000 |
+-------+---------+-----------+--------+
4 rows in set (0.08 sec)

In this example, we have used * to signify that we want to view all the columns from the table employee where salary is more than 70000. The resultset is sorted by first_name in ascending order.

Example – Select Individual Fields from a Table

Sometime we may need to view only specific information from a table as compared to the all information available in the table. Suppose, we want to know only Name and the Salary of employees from the above employee table.

Now let’s demonstrate how to use the SQL SELECT statement to select specific rows from a table. You can use the following SQL statement.

mysql> select name,salary from employee;
+---------+--------+
| name    | salary |
+---------+--------+
| Sagar   |  85000 |
| Shankar |  80000 |
| Kushal  |  70000 |
| Ranjit  |  60000 |
+---------+--------+
4 rows in set (0.06 sec)

In the above example, all the rows have been returned as we haven’t used any filter condition here. But notice that all the columns haven’t returned this time, only specific columns have been returned. We have selected only name and salary from the employee table which has been shown above.

Example – Select Individual Columns From Multiple Table

We can retrieve data specific columns from multiple tables using the SQL SELECT statement.

In this example, we have an employee table with the following data.

And the department table with the following data.

Now let’s select columns from these tables using SQL SELECT statement. You can follow the below statement.

mysql> select employee.empno, employee.name , department.dept_name
    -> from employee
    -> inner join department
    -> on employee.empno = department.emp_no
    -> order by employee.name ASC;
+-------+---------+------------+
| empno | name    | dept_name  |
+-------+---------+------------+
|     3 | Kushal  | Sales      |
|     4 | Ranjit  | Production |
|     1 | Sagar   | Maketing   |
|     2 | Shankar | Maketing   |
+-------+---------+------------+
4 rows in set (0.04 sec)

In this example, we have used inner join to get data from multiple tables. Columns empno and name have been selected from the employee table and dept_name has been selected from the department table. Also, the resultset has been sorted on name in ascending order.

If you want to select all the fields from the employee table and dept_name from the department table, you can use the following SQL statement.

mysql> select employee.*, department.dept_name
    -> from employee
    -> inner join department
    -> on employee.empno = department.emp_no
    -> order by employee.name ASC;
+-------+---------+-----------+--------+------------+
| empno | name    | city      | salary | dept_name  |
+-------+---------+-----------+--------+------------+
|     3 | Kushal  | Noida     |  70000 | Sales      |
|     4 | Ranjit  | Ahmedabad |  60000 | Production |
|     1 | Sagar   | Kolkata   |  85000 | Maketing   |
|     2 | Shankar | New Delhi |  80000 | Maketing   |
+-------+---------+-----------+--------+------------+
4 rows in set (0.00 sec)

In this example, employee.* denotes that we want to select all the columns from the employee table and dept_name from the department table.