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).
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. Selecting Specific Columns - Type and execute the following statements to read the data in the Products table.
-- 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.
-- Returns all columns in 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. 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
5. 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
6. 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
7. 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
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;
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;
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
