In this tutorial, you will learn about the most basic and most commonly used keyword and statement i.e. the SELECT keyword and the SELECT statement in SQL Server.
What is SELECT statement in SQL Server?
The SQL Server SELECT statement is used to fetch records from one or more SQL Server tables or views from the 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.
SQL Server SELECT statement is used to –
1. fetch data from all or one or more columns in a table.
2. fetch data from multiple columns in multiple tables.
Syntax
The basic syntax of SQL Server SELECT is as follows.
Select expressions FROM tables [WHERE conditions]
However, if we consider all the scenarios, the full syntax of SQL Server is as follows.
Select expressions
FROM tables
[WHERE conditions]
[GROUP BY expressions] [HAVING condition] [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 * in the place of expressions.
- 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.
- GROUP BY expressions – Optional. It performs group operation on the resultset by one or more columns.
- Having condition – Optional. This is used along with the GROUP BY expression to filter out the data. Having condition used to select only those records which met specific condition on GROUP BY.
- 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.
How SQL Server SELECT statement works?
In SQL Server the FROM clause is executed first and followed by SELECT although SELECT is written first.
SQL Server SELECT statement examples
Let us see below how it works starting from the simplest (i.e. single table) and gradually moving up in complexity (i.e. multiple tables).
NOTE: Columns are also referred to as fields and the terms are used interchangeably.
1) SQL Server SELECT – Select All the Column form a Table
Suppose, we have an employee table with the following data.
| empno | name | city | salary |
| 1 | Sagar | Kolkata | 85000 |
| 2 | Shankar | New Delhi | 80000 |
| 3 | Kushal | Noida | 70000 |
| 4 | Ranjit | Ahmedabad | 60000 |
Now, let’s check how the SELECT statement works by selecting all columns from the employee table.
SELECT * FROM employee;
Once you execute the above SELECT statement, you will see all the records in the resultset.
Alternatively, you can filter out columns using where clause as below.
select * from employee where salary > 75000 order by name;
Output
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 75000. The resultset is sorted by name in ascending order.
2) SQL Server SELECT – 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.
sql> 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.
3) SQL Server SELECT – 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.
| emp_no | name | city | salary |
| 1 | Sagar | Kolkata | 85000 |
| 2 | Shankar | New Delhi | 80000 |
| 3 | Kushal | Noida | 70000 |
| 4 | Ranjit | Ahmedabad | 60000 |
And the department table with the following data.
| dept_no | dept_name | emp_no |
| 1 | Production | 4 |
| 2 | Sales | 3 |
| 3 | Marketing | 1 |
| 4 | Marketing | 2 |
Now let’s select columns from these tables using SQL SELECT statement. You can follow the below statement.
sql> 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.
sql> 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.