Understanding T-SQL SELECT Statement


One of the most fundamental aspects of working with SQL Server is querying data stored inside a database. To query data from a database you use the SQL SELECT statement.  There are two important things to understand about the SELECT statement. The first is syntax, which are the rules and structure of the SQL statement that allows the statement to be sent to the database and have results returned. The second thing to understand is the “logical processing order” of select statements.  The way the SQL Server engine processes select statements to retrieve data is different from the order statement appears when you write syntactically SELECT statement in SQL Server Management Studio.

SELECT statements can be simple but can become very complex based on the needs and storage of the data inside the database.  You can find more information about the SELECT Syntax on Microsoft MSDN site.

Below you will find the basic syntax

T-SQL SYNTAX Logical Processing
SELECT <column1, column2,…> FROM
[FROM  <Table Name>] WHERE
[WHERE <Filter Conditions>] GROUP BY
[GROUP BY  <Columns used in Grouping>] HAVING
[HAVING  <Filter Conditions
on results frame>]
SELECT
[ORDER BY <Columns to be used for ordering> ] ORDER BY

The SELECT keyword is followed by the list of columns you want returned. This is then followed by the FROM clause, where you provide the name of the table you want to return, from the SELECT list of columns. This can then be followed by a WHERE predicate that allows you to filter rows return by the SELECT based on certain conditions. The GROUP BY clause is used, when an aggregate function is used in the SELECT statement, such as MAX, MIN or SUM. The HAVING clause is used as an additional layer of filtering based on conditions that are not known until the FROM, WHERE and GROUP BY clause has been processed by the SQL Engine. The HAVING clause works on what is know as a results partition. Finally the ORDER BY clause is used to provide sorting of the results sets. The ORDER BY clause allows for sorting columns in either ascending or descending order.

Although the SELECT keyword is the first statement, when writing a SQL statement, it is not the first statement processed by the SQL Engine. In fact the FROM clause is the first clause that is processed by the SQL Engine, Followed by the WHERE, GROUP BY and HAVING clauses. Once these clauses have been processed the SELECT statement is processed and finally the ORDER BY.  Understanding the syntax of SQL server and understanding the logical processing of query’s will help you write efficient query’s that provide the results you need for a particular solution.

Below are some basic select examples:

/*  Select   */
Select ProductID,Name, ListPrice 
From 
  Production.Product

figure_1

 
/*  Select With Where Clause   */
Select ProductID,Name, ListPrice  
From 
	Production.Product
Where  ListPrice >0

figure_2

/*Select to get a count of product by price*/
Select ListPrice,count(ProductID) as [NumOfProduct]
From 
	Production.Product 
Where  ListPrice >0
Group by ListPrice 
Order by [NumOfProduct] desc 

figure_3

 
/*Select to get a count of product by price where the listprice 
  Has more than 5 products with same price*/
Select ListPrice,count(ProductID) as [NumOfProduct]
From 
	Production.Product 
Where  ListPrice >0
Group by ListPrice 

Having count(ProductID) > 5
Order by [NumOfProduct] desc 

figure_4

/* Select to get a count of product by price where the listprice 
Has more than 5 products with same price*/
Select ListPrice,count(ProductID) as [NumOfProduct]
From 
	Production.Product 
Where  ListPrice >0
Group by ListPrice 
Having [NumOfProduct] > 5
Order by [NumOfProduct] desc 
 

figure_5

The above query fail with an error message “Invalid column Name ‘NumOfProduct’.” This error is caused because the Having clause is processed before the select. So therefore when this query executes the NumOfProduct column has not materialize so the SQL engine is unaware that it exist and throw an error. This can be fixed by referencing the expression in the having clause instead of the column alias.

Don’t forget to leave a comment.


Leave a Reply

Your email address will not be published. Required fields are marked *