SQL Server SELECT STATEMENT

Learning Objective

The objective of this tutorial is to help you to understand 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.

Purpose

A 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 SELECT is as follows.

Select expressions
FROM tables
[WHERE conditions]

However, if we consider all the scenario, 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.

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.

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.

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.