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:[table id=14 /][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[codesyntax lang=”tsql”]

SELECT * FROM Sales.Customer;

[/codesyntax]If the object (table, view, or column name) include irregular characters, such as space or other special characters, you must enclose the name of object by these two ways:

  • 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[codesyntax lang=”tsql”]

SELECT * FROM Sales.Customer;

[/codesyntax]Displaying Only Specified columns[codesyntax lang=”tsql”]

SELECT CustomerID,AccountNumber FROM Sales.Customer;

[/codesyntax]In addition, to retrieve columns stored in source table, a SELECT statement can perform calculations and manipulations. Calculated expressions in a SELECT statement should be scalar and return only a single value.Calculated Expression[codesyntax lang=”tsql”]

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

[/codesyntax]The Result set appear as follow:Scalar Calculators[table id=15 /][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[codesyntax lang=”tsql”]

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

[/codesyntax]The results:Calculated Column Result

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[codesyntax lang=”tsql”]

SELECT DISTINCT [Group] FROM Sales.SalesTerritory;

[/codesyntax]The result:DISTINCT Result

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[codesyntax lang=”tsql”]

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

[/codesyntax]

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

Alias with Equal Sign[codesyntax lang=”tsql”]

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

[/codesyntax]

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

Alias Following Column Name[codesyntax lang=”tsql”]

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

[/codesyntax]Aliases can also be used to rename a table in the FROM clause that can improve readability and redundancy.To create a table alias in a FROM clause, there are similar methods to column alias methods:

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

Table Alias with AS Keyword[codesyntax lang=”tsql”]

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

[/codesyntax]

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

Table Alias without AS Keyword[codesyntax lang=”tsql”]

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

[/codesyntax]Table and Column Aliases Combined

[codesyntax lang=”tsql”]

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

[/codesyntax]

[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

[codesyntax lang=”tsql”]

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

[/codesyntax]

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

[codesyntax lang=”tsql”]

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

[/codesyntax]

  • 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

[codesyntax lang=”tsql”]

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

[/codesyntax]

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.

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan