In this post, we will discuss about the structure and elements of the SELECT statement.

Elements of the SELECT Statement

In the following table you see the order of operations in the SELECT statement:

Source code

SELECT * FROM Sales.Customer;

• ANSI standard double quotes “Sales Order Details”
• The SQL Server specific square brackets [Sales Order Details]

End all statements with semicolon ( ; ) character, although it’s an optional terminator for most statement except for Common Table Expressions (CTEs) and Service Broker statements.To display columns in query, you need comma ( , ) character to delimit column list. T-SQL also supports asterisk, or star character ( * ) to retrieve all columns from source table. Asterisk is suitable for quick test but you should avoid using it in production code.Displaying All Columns

Source code

SELECT * FROM Sales.Customer;

Source code

SELECT CustomerID,AccountNumber FROM Sales.Customer;

Source code

SELECT UnitPrice, OrderQty, (UnitPrice * OrderQty) 
FROM Sales.SalesOrderDetail;

Source code

SELECT BusinessEntityID, HireDate, YEAR(HireDate) 
FROM HumanResources.Employee;

Eliminating Duplicates With DISTINCT

Sometimes T-SQL queries may display duplicate rows in results. SELECT DISTINCT will filter out duplicates and specify that the result set must include only unique rows. DISTINCT also operates on all columns in the SELECT list, not just on the first one.DISTINCT Statement

Source code

SELECT DISTINCT [GROUP] FROM Sales.SalesTerritory;

Column and Table Aliases

Column aliases can be used to rename a column title in result set. There are multiple methods to create a column alias:

• Use the AS keyword after column or expression and the alias name.

AS Keyword

Source code

SELECT SalesOrderID, UnitPrice, OrderQty AS Quantity
FROM Sales.SalesOrderDetail;

• Assign the alias before columns or expression and using the equal sign as a separator.

Alias with Equal Sign

Source code

SELECT SalesOrderID, UnitPrice, Quantity = OrderQty 
FROM Sales.SalesOrderDetail;

• Assign the alias immediately following the column or expression, although this method isn’t recommended.

Alias Following Column Name

Source code

SELECT SalesOrderID, UnitPrice, OrderQty Quantity
FROM Sales.SalesOrderDetail;

• Use the AS keyword to separate the table name from the alias.

Table Alias with AS Keyword

Source code

SELECT SalesOrderID, UnitPrice, OrderQty 
FROM Sales.SalesOrderDetail AS SOD;

• Omit the AS keyword and assign the alias following the table name.

Table Alias without AS Keyword

Source code

SELECT SalesOrderID, UnitPrice, OrderQty 
FROM Sales.SalesOrderDetail SOD;

Source code

SELECT SOD.SalesOrderID, SOD.UnitPrice, SOD.OrderQty AS Quantity 
FROM Sales.SalesOrderDetail SOD;

The Impact of logical Processing Order on Aliases

When using column or table aliases, it’s important to know, FROM, WHERE, and HAVING clauses processed before SELECT. Due to this logical order, column aliases in the SELECT clause can’t be referred to WHERE or HAVING clauses. Column aliases are only visible to ORDER BY clause.ORDER BY with Alias

Source code

SELECT SOD.SalesOrderID, SOD.UnitPrice, SOD.OrderQty AS Quantity 
FROM Sales.SalesOrderDetail SOD
ORDER BY Quantity;

Additionally, you may not refer to a column alias within the SELECT clause that was defined in the same SELECT statement

Case Expressions

In T-SQL, CASE expressions return a scalar value. They are used in SELECT (and other) clauses to return the result of an expression. A CASE expression is often used to provide an alternative user-meaning value instead of something stored as a compact numeric code in the source table.Two forms of T-SQL CASE expressions:

• Simple CASE:
  • Compare an input value to a list of possible values. The first matching value is returned as the result of the CASE expression. If no match is found, it returns the value in ELSE clause and if no ELSE clause is present, it returns a NULL as result.

Simple CASE Expression

Source code

SELECT SalesOrderID, 
	CASE OnlineOrderFlag 
	WHEN 1 THEN 'Online'
	WHEN 0 THEN 'In-Store'
	END AS OrderType,
	TotalDue
FROM Sales.SalesOrderHeader

• Searched (Boolean) CASE:
  • Compare a set of predictions, or logical expressions. The first matching expression that evaluates to TRUE, will return value found in THEN clause.

Searched CASE Expression

Source code

SELECT SalesOrderID, 
	CASE 
	WHEN TotalDue  5000 THEN 'Super Purchase'
	END AS PurchaseType,
	TotalDue
FROM Sales.SalesOrderHeader

In this post you learned how to write SELECT queries. By the way, at the end, consider to standard your code, by using semicolon terminator to protect your code against changes in future versions, and labeling appropriate aliases for columns and tables to make your queries easier to read.