SQL Server - SELECT Statement

In SQL Server, the SELECT statement is used to retrieve rows/columns data from one or more existing tables. It follows the SQL (Structured Query Language) standards.

Syntax:

SELECT column1, column2,...columnN 
FROM table_name

Select All Columns

The * operator represents all the columns of a table. So, you don't need to specify each column name in the SELECT query to get data from all the columns.

SELECT * FROM Employee;

The above query returns all the rows and columns data from the Employee table, as shown below.

Select Specific Columns Data

Specify the column names in the SELECT statement to get the data only from those columns, as shown below.

SELECT EmpId, FirstName, LastName FROM Employee;

The above query will display the following result.

Alias for Columns and Table

You can specify an alias for one or more columns in the SELECT query. An alias is a temporary name to a table or column in the query.

Advantage of Alias:

• Alias makes a column more readable in the result set.
• Alias is used to give a small, abbreviated, and meaningful name to tables in the query so that it will be easy to refer tables in joining multiple tables.
• Alias helps us to identify which column belongs to which table in case of getting data from multiple tables.

The following query specifies the "Employee Id" for EmpId, and Name as an alias for the FirstName column in the SELECT query. Specify an alias in the single or double quotes if you want a space or other string in it.

SELECT EmpId "Employee Id", FirstName AS Name FROM Employee;

The above query will display the following result.

+ Operator in SELECT Statement

The + operator in MS SQL Server concatenates string values or adds numeric values. The following concatenates two varchar columns in the result.

SELECT EmpId, FirstName + ' ' + LastName AS "Full Name" 
FROM Employee;

The above query will display the following result.

The following just sums up numbers in the select query.

SELECT 10 + 15; --returns 25
SELECT 10.5 + 15; --returns 25.5

FROM Clause

The SELECT statement must have the FROM clause. The FROM clause is used to list down table names from which we want to select data and specify joins between those tables.

You can specify multiple tables in the FROM clause to select data from. However, if tables have the same columns, then you need to specify full column names like table_name.column_name in the SELECT query.

The following selects columns from two tables.

SELECT * FROM Employee, Deparatment;

SELECT Employee.*, Department.* FROM Employee, Deparatment;

SELECT emp.*, dept.* FROM Employee emp, Deparatment dept; 

SELECT emp.FirstName, dept.DipartmentName FROM Employee emp, Deparatment dept;

Having multiple tables in the FROM clause without the WHERE clause and JOIN would return repeated data from each table. For example, if the Employee table has two rows and the Department table has two rows, then the above query would return four rows where columns of one table would repeat for the columns of another table. You will learn more about it in the JOIN tutorial.