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:
[info] The FROM, WHERE, GROUP BY, and HAVING clauses are evaluated by query engine before the contents of SELECT clause. This means that elements you write in the SELECT clause such as aliases and computed columns will not be visible to other clauses.[/info]The SELECT clause specifies the columns to retrieve. You can also add calculated expressions in the SELECT clause.The FROM clause specifies the name of table or view that is the source of the columns in the SELECT clause. To avoid errors, it’s the best to specify the table or view name by schema and object name, in this format: SCHEMA.OBJECT
CLAUSE EXPRESSION
SELECT select list
FROM table or view
WHERE search conditions
GROUP BY group by list
HAVING search conditions on group
ORDER BY order by list
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 ColumnsSELECT * FROM Sales.Customer;
SELECT CustomerID,AccountNumber FROM Sales.Customer;
SELECT UnitPrice, OrderQty, (UnitPrice * OrderQty)
FROM Sales.SalesOrderDetail;
Scalar Calculators
[info] Calculated columns don’t represent a name for returned columns to provide a name you need a column alias.[/info]There is another kind of computed column that built by T-SQL Functions which use the name of the column as an input.Create a Calculated Column
Operator Description
+ Add or Concatenate
- Subtract
* Multiply
/ Divide
% Modulo
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 StatementSELECT 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 KeywordSELECT 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 SignSELECT 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 NameSELECT SalesOrderID, UnitPrice, OrderQty Quantity
FROM Sales.SalesOrderDetail;
- Use the AS keyword to separate the table name from the alias.
Table Alias with AS KeywordSELECT SalesOrderID, UnitPrice, OrderQty
FROM Sales.SalesOrderDetail AS SOD;
- Omit the AS keyword and assign the alias following the table name.
Table Alias without AS KeywordSELECT SalesOrderID, UnitPrice, OrderQty
FROM Sales.SalesOrderDetail SOD;
SELECT SOD.SalesOrderID, SOD.UnitPrice, SOD.OrderQty AS Quantity
FROM Sales.SalesOrderDetail SOD;[info] in table alias you can’t use equal sign.[/info]
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
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
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
SELECT SalesOrderID,
CASE
WHEN TotalDue 5000 THEN 'Super Purchase'
END AS PurchaseType,
TotalDue
FROM Sales.SalesOrderHeaderIn 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.
