The SELECT statement is used to read the data in a table. It retrieves rows from the database and allows the selection of one or many rows or columns from one or many tables. The SELECT statement is one of the most important and most common Transact-SQL statements you will use. As we dig deeper, you will soon realize how powerful and complicated SELECT statements can get.
The basic syntax of the SELECT statement is as follows:
SELECT select_list
FROM table_source
WHERE search_condition
ORDER BY order_by_list
The four clauses of the SELECT statement:
SELECT Describes the columns that will be included in the result set.
FROM Names the base table(s) from which the query will retrieve the data.
WHERE Specifies any conditions that must be met for a row to be included in the result set (filter).
ORDER BY Sets how the rows in the result set will be sorted (ordered).
Due to the complexity of the SELECT statement, detailed syntax elements and arguments are shown by clause:
SELECT Clause - Specifies the columns to be returned by the query.
INTO Clause - Creates a new table and inserts the resulting rows from the query into it.
FROM Clause - Specifies the table(s) from which to retrieve rows. The FROM clause is required in every select statement that retrieves data from tables or views, except when the select list contains only constants, variables, and arithmetic expressions (no column names).
WHERE Clause - Specifies a search condition to restrict the rows returned.
GROUP BY Clause - Specifies the groups into which output rows are to be placed and, if aggregate functions are included in the SELECT clause, calculates a summary value for each group. When a GROUP BY clause is used, each item in the select list must produce a single value for each group. Null values in one item are placed in one group.
HAVING Clause - Specifies a search condition for a group or an aggregate. HAVING is usually used with the GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.
UNION Operator - Combines the results of two or more queries into a single result set consisting of all the rows belonging to all queries in the union.
ORDER BY Clause - Specifies the sort for the result set.
Subqueries and view definitions cannot include an ORDER BY clause.
COMPUTE Clause - Generates totals that appear as additional summary columns at the end of the result set.
///////////////////
To Query the Northwind Database, Customers SQL table and view the results, simply use the syntax:
-- Select rows from table 'Customers'
SELECT * FROM dbo.Customers;
A SELECT statement contains the common elements used in Transact-SQL statements. For example, to select the names, contact names, and telephone numbers of customers who live in the USA from the Customers table in the Northwind database, these elements are used:
The name of the database containing the table (Northwind)
The name of the table containing the data (Customers)
A list of the columns for which data is to be returned (CompanyName, ContactName, Phone)
Selection criteria (only for customers living in the USA)
There are many variations in the SELECT statement syntax. Below are five simple versions of the SELECT statement. In the examples below, we will be reading the data in the Products table of the AdventureWorks Lightweight (LT) sample database.
SQL SELECT Examples - All the examples below uses the AdventureWorks Lightweight (LT) download Sample Database.
//////////////////////1. Choose Specific Columns - Type and execute the following statements to read the data from specified columns in the Products table.
Select specific columns in a table by separating column names with commas.
-- The basic syntax to retrieve specific columns from a single table
SELECT ProductNumber, Name, StandardCost, ListPrice
FROM SalesLT.Product;
GO
2. Using SELECT * to retrieve all columns. - You can use an asterisk to select all the columns in the products table.
When the select list consists of a column name, a group of columns, or the wildcard character (*), the data is retrieved in the order in which it is stored in the table
-- This code example returns all rows (no WHERE clause is specified) and all columns (using the *) from the products table.
SELECT * FROM SalesLT.Product;
GO
3. You can omit columns that you do not want to return. The columns will be returned in the order that they are listed.
-- Returns only two of the columns from the table
SELECT Name, ListPrice
FROM SalesLT.Product;
GO
4. Rearrange the Column Order - You can specify column order the way you want to see them returned. The columns will be returned in the order that they are listed.
-- Returns the columns in a specified order in the query results
SELECT ListPrice, Name
FROM SalesLT.Product;
GO
5. Rename Columns in Query Results - Rename a column heading for display purposes.
To rename a column heading, use one of these options instead of the column name in a select list:
column_heading = column_name
column_name column_heading
column_name as column_heading
This provides a substitute name for the column. For example, to change Name to “Product Name” in the #3 previous query, type any of the following statements:
-- Renames a column heading for display purposes.
SELECT Product Name = Name, ListPrice FROM SalesLT.Product
SELECT Name Product Name, ListPrice FROM SalesLT.Product
SELECT Name as Product Name, ListPrice FROM SalesLT.Product
6. Quoted Strings in Column Headings - You can include any characters —including blanks— in a column heading if you enclose the entire heading in quotation marks.
-- Both of these queries will produce the same result
SELECT "Product's Name" = Name FROM SalesLT.Product
SELECT Name "Product's Name" FROM SalesLT.Product
7. Character Strings in Query Results - Write queries so that the results contain strings of characters.
Enclose the string in single or double quotation marks, and separate it from other elements in the select list with a comma. Use double quotation marks if there is an apostrophe in the string.
-- Query with a character string
SELECT "The Product's Name is", Product Name = Name
FROM SalesLT.Product
8. Create a list of all customer contact names that includes the title (if any), + last name, AS CustomerName, and Phone of all customers.
-- Returns only customer title (if any), lastname and phone numbers from the Customer table
SELECT ISNULL(Title,'') + ' ' + LastName AS CustomerName, Phone
FROM SalesLT.Customer;
GO
9. You can work with the values in the columns as they are returned. The following example performs a mathematical operation on the Price + Tax column. Columns that have been changed in this way will not have a name unless you provide one by using the AS keyword.
-- Returns Name, ListPrice and the Price including a 7% tax
-- Provides the name Price + Tax for the calculated column
SELECT Name, ListPrice, ListPrice * 1.07 AS 'Price + Tax'
FROM SalesLT.Product;
GO
Note
Refer to 5. Rename Columns in Query Results - for ways to give a computed column a heading.
10. The following example returns all rows from the DimEmployee table, and calculates the gross pay for each employee based on their BaseRate and a 40-hour work week.
E
SELECT FirstName, LastName, BaseRate, BaseRate * 40 AS GrossPay
FROM DimEmployee
ORDER BY LastName;
11. Here is another example of working with the values in the columns as they are returned. The following example results in a calculated column. This script performs a mathematical operation by subtracting the ListPrice column from the StandardCost column and using the AS keyword to assign an alias column named Profit Margin.
-- Returns Name, ListPrice - StandardCost AS Profit Margin
-- The AS keyword is used to assign an alias column named Profit Margin
SELECT Name, ListPrice - StandardCost AS 'Profit Margin'
FROM SalesLT.Product;
GO
Note
Null values have no explicitly assigned values. When you perform any arithmetic operation on a null value, the result is NULL.
12. Here is another example that produces a calculated column in the results. The following example results in a calculated column. The + operator in the calculated ProductDetails column is used to concatenate the Color and Size column values. The most important thing to get out of this example is the behavior of the + operator which is determined by the data types of the columns. With numeric values, the + operator or - operator would have added or subtracted the columns as in example 6.
However in this example, the + operator in the calculated ProductDetails column is used to concatenate the Color and Size column values with a literal comma between them.
-- Returns ProductNumber, Color, Size, (concatenated Color and Size) AS Product Details
-- The AS keyword is used to assign an alias column named Product Details
SELECT ProductNumber, Color, Size, Color + ', ' + Size AS 'Product Details'
FROM SalesLT.Product;
GO
When there is more than one arithmetic operator in an expression; multiplication, division, and modulo are calculated first, followed by subtraction and addition.
If all arithmetic operators in an expression have the same level of precedence, the order of execution is left to right.
Expressions in parentheses take precedence over all other operations.
With Arithmetic Operators, parentheses are used to change the order of execution.
calculations inside parentheses are handled first.
If parentheses are nested, the most deeply nested calculation has precedence.
| Operator | Meaning |
|---|---|
| + (Add) | Addition |
| - (Subtract) | Subtraction |
| * (Multiply) | Multiplication |
| / (Divide) | Division |
| % (Modulo) | Returns the integer remainder of a division. For example, 12 % 5 = 2 because the remainder of 12 divided by 5 is 2. |
The example below retrieves the productID, Name, the List Price and the new list Price of all the mountain bicycles in the Product table. The new List Price is calculated by using the * arithmetic operator to multiply ListPrice by 1.15.
-- Uses AdventureWorksLT
SELECT ProductID, Name, ListPrice, ListPrice * 1.15 AS NewPrice
FROM Production.Product
WHERE Name LIKE 'Mountain-%'
ORDER BY ProductID ASC;
GO
To delete limited records in the database, use the WHERE clause along with DELETE command. For example:
///////////////////New Copy Code //////////////////////////Another example:
USE Northwind
GO
DELETE FROM Employee WHERE Employee_Id IN(3,5);
GO
Note
.
GO
This section shows three code examples. This first code example returns all rows (no WHERE clause is specified) and all columns (using the *) from the DimEmployee table.
A
SELECT *
FROM DimEmployee
ORDER BY LastName;
The example below uses table aliasing to achieve the same result.
B
SELECT e.*
FROM DimEmployee AS e
ORDER BY LastName;
This NEXT example returns all rows (no WHERE clause is specified) and a subset of the columns (FirstName, LastName, StartDate) from the DimEmployee table in the AdventureWorksPDW2012 database. The third column heading is renamed to FirstDay.
C
SELECT FirstName, LastName, StartDate AS FirstDay
FROM DimEmployee
ORDER BY LastName;
This example returns only the rows for DimEmployee that have an EndDate that is not NULL and a MaritalStatus of 'M' (married).
D
SELECT FirstName, LastName, StartDate AS FirstDay
FROM DimEmployee
WHERE EndDate IS NOT NULL
AND MaritalStatus = 'M'
ORDER BY LastName;
