SQL Server Helper - Tip of the Day

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