SELECT (Transact-SQL)

Last updated: October 17, 2023

Retrieves records from one or more tables and allows you to specify the order in which rows are returned.

In This Article

Syntax

SELECT
    [ ALL | DISTINCT ]
    select_list
    [ INTO new_table ]
    [ FROM
        [ table_source [, ...]
        | derived_table [ AS ] alias
        | table_function( arguments ) [ AS ] alias
        ]
    ]
    [ WHERE search_condition ]
    [ GROUP BY
        { group_by_expression [ WITH { CUBE | ROLLUP } ] [ ,...n ] }
    ]
    [ HAVING search_condition ]
    [ ORDER BY
        { order_by_expression [ ASC | DESC ] [ ,...n ] }
    ]
    [ OFFSET { integer_constant | rows_variable } { ROW | ROWS }
        [ FETCH { FIRST | NEXT } { integer_constant | rows_variable } { ROW | ROWS }
            { ONLY | WITH TIES }
        ]
    ]
    [ OPTION ( query_option [ ,...n ] ) ]

Description

The SELECT statement is the fundamental statement for retrieving data from a relational database. It allows you to query data from one or more tables and presents it in a result set.

Key Components:

  • ALL: Returns all rows matching the criteria, including duplicates. This is the default.
  • DISTINCT: Returns only unique rows.
  • select_list: Specifies the columns or expressions to be returned. You can use column names, arithmetic expressions, scalar functions, or even subqueries. Use * to select all columns.
  • INTO new_table: Creates a new table and inserts the result set into it.
  • FROM: Specifies the source table(s) for the data. You can join multiple tables using various join types (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN).
  • WHERE: Filters the rows based on specified conditions.
  • GROUP BY: Groups rows that have the same values in specified columns into a summary row. Used with aggregate functions like COUNT, SUM, AVG, MAX, MIN.
  • HAVING: Filters groups based on specified conditions. It's like WHERE but for groups created by GROUP BY.
  • ORDER BY: Sorts the result set in ascending (ASC) or descending (DESC) order based on one or more columns.
  • OFFSET...FETCH: Used for pagination to skip a specified number of rows before returning the result set.
  • OPTION: Allows you to specify query hints to influence query processing.
Tip: Use SELECT * judiciously, especially in production environments, as it can impact performance and make your queries less readable. It's generally better to explicitly list the columns you need.

Permissions

To execute a SELECT statement, the user requires the SELECT permission on the object(s) being queried.

Examples

Example 1: Select all columns from a table

SELECT *
FROM Sales.Customer;

Example 2: Select specific columns with an alias

SELECT CustomerID AS CustID, FirstName, LastName
FROM Sales.Customer;

Example 3: Filter rows using WHERE

SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ListPrice > 50 AND ListPrice < 100;

Example 4: Group and count

SELECT CountryRegionCode, COUNT(CustomerID) AS NumberOfCustomers
FROM Sales.Customer
GROUP BY CountryRegionCode
ORDER BY NumberOfCustomers DESC;

See Also