In this post, we will discuss how to write a SELECT statement in T-SQL. In the picture above we see the six main keywords. For this post, we will only cover the SELECT and FROM keywords. You can read about the WHERE, ORDER BY, GROUP BY, and HAVING in their specific posts.
But the first two important notes, the order that we write our statement is not the same order as SQL processes them. You will notice in the picture above, that the SELECT statement is the fifth line of code that will be processed. This is because we can’t SELECT columns from a table if we don’t know FROM which table those columns exist.
Secondly, the SELECT statement is the only required keyword to return data. For example, we could use the SELECT statement to perform math or return a date without the need to specify a table.
SELECT 2 + 2 SELECT GETDATE()
The first line in the code above would return a value of 4, while the second statement is using a function to return today’s date. If we wanted to return data from a table, we would use the FROM keyword as shown in the following code statement.
SELECT * FROM Production.Product
In the code above we are using an asterisk (*) to return all the columns FROM the Production.Product table. Note: We could write the entire statement on a single line, but for readability, the FROM keyword can be placed on the second line. It is best practice to avoid using the asterisk to return all columns as it returns more data than is needed and could slow performance for other queries. Additionally, we are referencing the table in this code only using two parts of the table name. If you receive an error, make sure you are connected to the AdventureWorks database or use the four-part name of the table. Next, we look at selecting specific columns from the Product table.
The column list (Sometimes known as the select list) will specify which columns to return from the table with each column being separated by a comma. This statement will return all the records from the Product table because a WHERE keyword has not been included to filter out the rows.
A final note, when you write your SELECT statements your keywords will show in blue, predicates in grey, and functions in pink. Although there are exceptions, for example, the keywords LEFT and RIGHT are both predicates and functions and will display in grey. In our code above you will notice that in our select list the NAME column is displaying in blue. That is because it is a SQL keyword, but the person who made the Production.Product table in the AdventureWorks database also used it as a column name. This is bad practice. Try to avoid using keywords when naming columns, tables, or any other object.