Skip to content

SQL Server Tutorial - SELECT Statement 101

The SELECT statement is used to retrieve rows from the database and enables the selection of one or many rows and one or many columns from one or many tables.

The full syntax of the SELECT statement is complex but it can be broken down into the following clauses or elements:

  • WITH <common_table_expression>
  • SELECT Clause
  • INTO Clause
  • FROM Clause
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • UNION Clause
  • EXCEPT and INTERSECT Clause
  • ORDER BY Clause
  • FOR Clause
  • OPTION Clause

Here are a few examples of how to use the SELECT statement:

  • Using SELECT to Retrieve All Rows and All Columns from a Table
SELECT * FROM [dbo].[Sales]
  • Using SELECT to Retrieve All Columns From a Table But Only Some Rows Based on a Condition
SELECT * FROM [dbo].[Sales]
WHERE [TransactionAmount] > 1000.00
  • Using SELECT to Return Rows from a Table in Ascending Order
SELECT * FROM [dbo].[Customer]
ORDER BY [CustomerName] ASC
  • Using SELECT to Return Rows from a Table in Descending Order
SELECT * FROM [dbo].[Sales]
ORDER BY [SalesAmount] DESC
  • Using SELECT to Return DISTINCT or Unique Rows
SELECT DISTINCT [ProductName], [UnitPrice], [Quantity]
FROM [dbo].[Product]
  • Using SELECT to Return Specified Number of Rows
SELECT TOP 100 [ProductName], [UnitPrice], [Quantity]
FROM [dbo].[Product]
  • Using SELECT to Return Literal Values Instead of From a Table
SELECT 'USD' AS [CurrencyCode], 'US Dollar' AS [CurrencyName]
UNION ALL
SELECT 'CAD' AS [CurrencyCode], 'Canadian Dollar' AS [CurrencyName]
UNION ALL
SELECT 'EUR' AS [CurrencyCode], 'Euro' AS [CurrencyName]
  • Using SELECT to Assign Literal Values to Local Variables
DECLARE @Index        INT
DECLARE @CountryCode  CHAR(2)

SELECT @Index = 100, @CountryCode = 'US'
  • Using SELECT to Assign Table Values to Local Variables
DECLARE @FirstName      VARCHAR(50)
DECLARE @LastName       VARCHAR(50)

SELECT @FirstName = [FirstName], @LastName = [LastName]
FROM [dbo].[User]
WHERE [UserName] = 'sqlserver'
  • Using SELECT to Retrieve Certain Number of Rows that Satisfy a Condition in a Specified Order
SELECT TOP 100 [EmployeeNumber], [FirstName], [LastName]
FROM [dbo].[Employees]
WHERE [HiringDate] >= '2012/01/01'
ORDER BY [HiringDate] DESC, [LastName] ASC, [FirstName] ASC