SELECT Statement
SELECT statement provides ability to retrieve data from the database in a tabular format.
The most basic SELECT statement to retrieve data consists of a SELECT clause and a FROM clause.
SYNTAX:
SELECT Column1, Column2, Column3 …
FROM Table1
A more common SELECT statement includes a WHERE clause to filter data by including search conditions.
SYNTAX:
SELECT Column1, Column2, Column3 …
FROM Table1
WHERE [Condition]
SELECT Clause
  • Can include column names, constants, variables or scalar functions.
  • To display data from specific columns, comma separated column names can be included in the Select clause.
  • To retrieve data from all columns, asterisk (“*”) can be used instead of column names in the Select clause.
  • To exclude duplicate records in the result set, DISTINCT keyword can be used in the Select clause.
  • To limit number of records in the result set, TOP keyword followed by number of records to be retrieved from the table can be included in the Select clause.
  • A maximum of 4096 columns can be added in the Select clause.
FROM Clause
  • Tables, views and table valued functions from which data needs to be retrieved are included in the From clause.
  • If Select clause only includes constants, variables or scalar functions, From clause is optional in the Select Statement.
  • For retrieving data from more than one column, JOIN condition can be used in the From clause.
WHERE Clause
  • Where clause is used in the Select statement to filter data from the final result set by adding search conditions.
  • Where clause is optional in the Select Statement.
  • There are no limits to the number of search conditions which can be included in the Where clause.
Table: Employee
EmployeeID FirstName LastName Title HireDate ManagerID
1 George Cruz CEO 12/8/2008 NULL
2 John Mathew Vice President 4/1/2009 1
3 Ethan William Director I 8/8/2009 2
4 Jacob Logan Director II 12/8/2011 3
5 Nathan Mateo Senior Manager 9/3/2009 4
6 Alice Charles Manager I 12/8/2012 5
7 John Robert Manager II 10/8/2011 6
8 Sophia James Manager III 5/8/2011 7
9 Kevin Chan Product Manager 7/8/2011 5
10 Susan Mortiz Solutions Arthitect 1/3/2012 6
11 Martha Miller Systems Arthitect 12/15/2012 10
Example 1 (Select specific columns)
Query :
SELECT EmployeeID, FirstName, LastName
FROM Employee
Output :
EmployeeID FirstName LastName
1 George Cruz
2 John Mathew
3 Ethan William
4 Jacob Logan
5 Nathan Mateo
6 Alice Charles
7 John Robert
8 Sophia James
9 Kevin Chan
10 Susan Mortiz
11 Martha Miller
Description :
The above query retrieves data from EmployeeID, FirstName and LastName columns in employee table.
Example 2 (Select all columns)
Query :
SELECT *
FROM Employee
Output :
EmployeeID FirstName LastName Title HireDate ManagerID
1 George Cruz CEO 12/8/2008 NULL
2 John Mathew Vice President 4/1/2009 1
3 Ethan William Director I 8/8/2009 2
4 Jacob Logan Director II 12/8/2011 3
5 Nathan Mateo Senior Manager 9/3/2009 4
6 Alice Charles Manager I 12/8/2012 5
7 John Robert Manager II 10/8/2011 6
8 Sophia James Manager III 5/8/2011 7
9 Kevin Chan Product Manager 7/8/2011 5
10 Susan Mortiz Solutions Arthitect 1/3/2012 6
11 Martha Miller Systems Arthitect 12/15/2012 10
Description :
The above query retrieves data from all the columns in employee table.
Example 3 (Where clause)
Query :
SELECT *
FROM Employee
WHERE EmployeeID < 5
AND Title = 'CEO'
Output :
EmployeeID FirstName LastName Title HireDate ManagerID
1 George Cruz CEO 12/8/2008 NULL
Description :
The above query retrieves all columns from the employee table using asterisk (“*”) in the SELECT clause. The query also uses WHERE clause to filter data. The WHERE clause uses two Comparison operators ("<" and "=") and one Logical operator (“AND”).