SELECT Statement (Transact-SQL)

The SELECT statement is the primary statement used to retrieve data from one or more tables in a SQL Server database.

Syntax


SELECT
    [ ALL | DISTINCT ]
    
    [ INTO new_table ]
    [ FROM
        { <table_source> } [ ,...n ]
    ]
    [ WHERE
        
    ]
    [ GROUP BY
        [ ALL ] <group_by_expression> [ ,...n ]
    ]
    [ HAVING
        
    ]
    [ ORDER BY
         [ ASC | DESC ] [ ,...n ]
    ]
    [  ]
    [ OPTION ( <query_option> [ ,...n ] ) ]
;

Parameters

The following section describes the arguments of the SELECT statement.

<select_list>

Specifies the columns or expressions to be returned by the query. You can select all columns by using *.

SELECT * FROM Customers;
SELECT CustomerID, CompanyName FROM Customers;

INTO new_table

Creates a new table and inserts the result set of the query into it. This clause is an extension of the standard SQL SELECT statement.

SELECT CustomerID, CompanyName INTO NewCustomers FROM Customers WHERE Country = 'USA';

FROM <table_source>

Specifies the tables or views from which to retrieve rows. You can join multiple tables using various join types.

SELECT Orders.OrderID, Customers.CompanyName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

WHERE <search_condition>

Specifies the conditions that rows must meet to be included in the result set. You can use comparison operators, logical operators, and other expressions.

SELECT * FROM Products WHERE UnitPrice > 50 AND CategoryID = 1;

GROUP BY <group_by_expression>

Groups rows that have the same values in specified columns into summary rows.

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;

HAVING <search_condition>

Specifies conditions for grouping rows or aggregate functions. HAVING filters groups based on a specified condition, whereas WHERE filters individual rows.

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 10;

ORDER BY <order_by_expression>

Sorts the rows in the result set in ascending (ASC) or descending (DESC) order.

SELECT ProductName, UnitPrice FROM Products ORDER BY UnitPrice DESC;

<offset_fetch>

Introduced in SQL Server 2012, this clause allows you to specify the number of rows to skip and the number of rows to return, enabling pagination.

SELECT CustomerID, CompanyName
FROM Customers
ORDER BY CustomerID
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

Examples

Basic Select

Retrieves all columns and rows from the Employees table.

SELECT * FROM Employees;

Selecting Specific Columns and Filtering

Retrieves the ProductName and UnitPrice for products in the 'Beverages' category, sorted by price.

SELECT ProductName, UnitPrice
FROM Products
WHERE CategoryID = (SELECT CategoryID FROM Categories WHERE CategoryName = 'Beverages')
ORDER BY UnitPrice DESC;

Joining Tables and Aggregation

Counts the number of orders placed by each customer and displays the customer's company name. Only customers with more than 5 orders are shown.

SELECT C.CompanyName, COUNT(O.OrderID) AS NumberOfOrders
FROM Customers AS C
JOIN Orders AS O ON C.CustomerID = O.CustomerID
GROUP BY C.CompanyName
HAVING COUNT(O.OrderID) > 5
ORDER BY NumberOfOrders DESC;

Microsoft Learn

Documentation