Summary: This tutorial introduces you to the basics of the SQL Server SELECT statement, focusing on how to retrieve data from a table.

Basic SQL Server SELECT statement

Database tables are objects that store all the data in a database. In a table, data is logically organized in a row-and-column format, similar to a spreadsheet.

Each row represents a unique record in a table, and each column represents a field in the record.

For example, the following customers table contains customer data such as customer identification number, first name, last name, phone, email, and address information:

SQL Server uses schemas to logically group tables and other database objects. In our sample database, we have two schemas: sales and production.

The sales schema groups all the sales-related tables, while the production schema groups all the production-related tables.

To retrieve data from a table, you use the SELECT statement. Below is the most basic form of the SELECT statement:

SELECT
    select_list
FROM
    schema_name.table_name;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify a list of comma-separated columns from which you want to query data in the SELECT clause.
• Second, specify the table name and its schema in the FROM clause.

When processing the SELECT statement, SQL Server first processes the FROM clause, followed by the SELECT clause, even though the SELECT clause appears first in the query:

SQL Server SELECT statement examples

Let’s use the customers table in the sample database for the demonstration.

1) Basic SQL Server SELECT statement example

The following query uses a SELECT statement to retrieve the first name and last name of all customers:

SELECT
    first_name,
    last_name
FROM
    sales.customers;Code language: SQL (Structured Query Language) (sql)

Here is the result:

The result of a query is called a result set.

The following statement uses the SELECT statement to retrieve the first name, last name, and email of all customers:

SELECT
    first_name,
    last_name,
    email
FROM
    sales.customers;Code language: SQL (Structured Query Language) (sql)

Output:

2) Using the SQL Server SELECT to retrieve all columns of a table

To retrieve data from all table columns, you can specify all the columns in the SELECT list. Alternatively, you can also use SELECT * as a shorthand to select all columns:

SELECT * FROM sales.customers;Code language: SQL (Structured Query Language) (sql)

The SELECT * helps is useful for examining the columns and data of a table that you are not familiar with, and it’s particularly helpful for ad-hoc queries.

However, you should not use the SELECT * for production code due to the following reasons:

1. First, SELECT * often retrieves more data than your application needs to function. It causes unnecessary data to transfer from the SQL Server to the client application, taking more time for data to travel across the network and slowing down the application.
2. Second, if one or more new columns are added to the table, using the SELECT * retrieves all columns, including the newly added columns that may not have been intended for use in the application. This could potentially cause the application application to crash.

3) Filtering rows using the WHERE clause

To filter rows based on one or more conditions, you use a WHERE clause. For example:

SELECT
    *
FROM
    sales.customers
WHERE
    state = 'CA';Code language: SQL (Structured Query Language) (sql)

In this example, the query returns the customers located in California.

When the WHERE clause is present, SQL Server processes the clauses of the query in the following sequence: FROM, WHERE, and SELECT.

4) Sorting rows using the ORDER BY clause

To sort the rows in a result set based on one or more columns, you use the ORDER BY clause. For example:

SELECT
    *
FROM
    sales.customers
WHERE
    state = 'CA'
ORDER BY
    first_name;Code language: SQL (Structured Query Language) (sql)

In this example, the ORDER BY clause sorts the customers by their first names in ascending order.

In this case, SQL Server processes the clauses of the query in the following sequence: FROM, WHERE, SELECT, and ORDER BY.

5) Grouping rows into groups

To group rows into groups, you use the GROUP BY clause. For example, the following statement returns all the cities of customers located in California and the number of customers in each city.

SELECT
    city,
    COUNT (*)
FROM
    sales.customers
WHERE
    state = 'CA'
GROUP BY
    city
ORDER BY
    city;
Code language: SQL (Structured Query Language) (sql)

In this case, SQL Server processes the clauses in the following sequence: FROM, WHERE, GROUP BY, SELECT, and ORDER BY.

6) Filtering groups using the HAVING clause

To filter groups based on one or more conditions, you use the HAVING clause. For example, the following statement use the HAVING clause to return the city in California which has more than ten customers:

SELECT
    city,
    COUNT (*)
FROM
    sales.customers
WHERE
    state = 'CA'
GROUP BY
    city
HAVING
    COUNT (*) > 10
ORDER BY
    city;
Code language: SQL (Structured Query Language) (sql)

Notice that the WHERE clause filters rows while the HAVING clause filter groups.

Summary

• Use the SQL Server SELECT statement to retrieve data from a table.