SQL Server: SELECT Statement

In this post explains how to use the SELECT statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) SELECT statement is used to retrieve records from one or more tables in a SQL Server database.

Syntax

In its simplest form, the syntax for the SELECT statement in SQL Server (Transact-SQL) is:

SELECT expressions
FROM tables
[WHERE conditions];

However, the full syntax for the SELECT statement in SQL Server (Transact-SQL) is:

SELECT [ ALL | DISTINCT ]
[ TOP (top_value) [ PERCENT ] [ WITH TIES ] ]
expressions
FROM tables
[WHERE conditions]
[GROUP BY expressions]
[HAVING condition]
[ORDER BY expression [ ASC | DESC ]];

Parameters or Arguments

ALL

Optional. Returns all matching rows.

DISTINCT

Optional. Removes duplicates from the result set. Learn more about the DISTINCT clause

TOP (top_value)

Optional. If specified, it will return the top number of rows in the result set based on top_value. For example, TOP(10) would return the top 10 rows from the full result set.

PERCENT

Optional. If specified, then the top rows are based on a percentage of the total result set (as specfied by the top_value). For example, TOP(10) PERCENT would return the top 10% of the full result set.

WITH TIES

Optional. If specified, then rows tied in last place within the limited result set are returned. This may result in more rows be returned than the TOP parameter permits.

expressions

The columns or calculations that you wish to retrieve. Use * if you wish to select all columns.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be selected.

GROUP BY expressions

Optional. It collects data across multiple records and groups the results by one or more columns.

HAVING condition

Optional. It is used in combination with the GROUP BY to restrict the groups of returned rows to only those whose the condition is TRUE.

ORDER BY expression

Optional. It is used to sort the records in your result set. ASC sorts in ascending order and DESC sorts in descending order.

Example - Select all fields from one table

Let's look at how to use a SQL Server SELECT query to select all fields from a table.

SELECT *
FROM inventory
WHERE quantity > 5 
ORDER BY inventory_id ASC;

In this SQL Server SELECT statement example, we've used * to signify that we wish to select all fields from the inventory table where the quantity is greater than 5. The result set is sorted by inventory_id in ascending order.

Example - Select individual fields from one table

You can also use the SQL Server SELECT statement to select individual fields from the table, as opposed to all fields from the table.

For example:

SELECT inventory_id, inventory_type, quantity
FROM inventory
WHERE inventory_id >= 555
AND inventory_type = 'Software'
ORDER BY quantity DESC, inventory_id ASC;

This SQL Server SELECT example would return only the inventory_id, inventory_type, and quantity fields from the inventory table where the inventory_id is greater than or equal to 555 and the inventory_type is 'Software'. The results are sorted by quantity in descending order and then inventory_id in ascending order.

Example - Select fields from multiple tables

You can also use the SQL Server SELECT statement to retrieve fields from multiple tables by using a join.

For example:

SELECT inventory.inventory_id, products.product_name, inventory.quantity
FROM inventory
INNER JOIN products
ON inventory.product_id = products.product_id
ORDER BY inventory_id;

This SQL Server SELECT example joins two tables together to gives us a result set that displays the inventory_id, product_name, and quantity fields where the product_id value matches in both the inventory and products table. The results are sorted by inventory_id in ascending order.

Example - Using TOP keyword

Let's look at a SQL Server example, where we use the TOP keyword in the SELECT statement.

For example:

SELECT TOP(3)
inventory_id, inventory_type, quantity
FROM inventory
WHERE inventory_type = 'Software'
ORDER BY inventory_id ASC;

This SQL Server SELECT example would select the first 3 records from the inventory table where the inventory_type is 'Software'. If there are other records in the inventory table that have a inventory_type value of 'Software', they will not be returned by the SELECT statement.

Example - Using TOP PERCENT keyword

Let's look at a SQL Server example, where we use the TOP PERCENT keyword in the SELECT statement.

For example:

SELECT TOP(10) PERCENT
inventory_id, inventory_type, quantity
FROM inventory
WHERE inventory_type = 'Software'
ORDER BY inventory_id ASC;

This SQL Server SELECT example would select the first 10% of the records from the full result set. So in this example, the SELECT statement would return the top 10% of records from the inventory table where the inventory_type is 'Software'. The other 90% of the result set would not be returned by the SELECT statement.