Writing SELECT Queries

The estimated reading time for this post is 5 minutes

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:

CLAUSEEXPRESSION
SELECTselect list
FROMtable or view
WHEREsearch conditions
GROUP BYgroup by list
HAVINGsearch conditions on group
ORDER BYorder by list

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.

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

SELECT * FROM Sales.Customer;

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

SELECT * FROM Sales.Customer;

Displaying Only Specified columns

SELECT CustomerID,AccountNumber FROM Sales.Customer;

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

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

The Result set appear as follow:

image-2945

Scalar Calculators

OperatorDescription
+Add or Concatenate
-Subtract
*Multiply
/Divide
%Modulo

Calculated columns don’t represent a name for returned columns to provide a name you need a column alias.

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

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

The results:

Calculated Column Result
image-2946

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

SELECT DISTINCT [GROUP] FROM Sales.SalesTerritory;

The result:

DISTINCT Result
image-2947

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

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

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

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

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

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

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

Table and Column Aliases Combined

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

in table alias you can’t use equal sign.

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 < 1000 THEN 'Standard Purchase'
	WHEN TotalDue between 1000 and 5000 THEN 'High Purchase'
	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.

Hamide B. Abshuri

I am SQL Server Developer in Fard Solutions Company.

More Posts - Website


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz