SQL Server SELECT

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

Basic SQL Server SELECT statement

In SQL Server, tables are objects that store all the data in a database. They organize data 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 that record.

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

SQL Server uses schemas to logically group tables and other database objects. For example, our sample database has two schemas: sales and production.

The sales schema includes 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 with the following syntax:

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 before the FROM clause:

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 and last names 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 often 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)

Using the SELECT * is useful for examining the table that you are not familiar with and it is particularly helpful for ad-hoc queries.

However, you should not use the SELECT * in production code for the following main reasons:

• First, using SELECT * often retrieves more data than your application needs. This unnecessary data takes more time to transfer from the database server to the application, slowing down the application.
• Second, if new columns are added to the table, SELECT * will retrieve all columns, including the new ones that your application may not expect. This could potentially cause the application to behave unexpectedly.

In the following section, we’ll briefly introduce the additional clauses of the SELECT statement:

• WHERE : filter rows in the result set.
• ORDER BY: sort rows in the result set by one or more columns.
• GROUP BY: group rows into groups.
• HAVING: filter groups.

Please note that we’ll cover these clauses in greater detail in the upcoming tutorials.

3) Filtering rows using the WHERE clause

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

For example, the following SELECT statement uses a WHERE clause to find customers located in California:

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

If the SELECT statement includes both WHERE and FROM clauses, SQL Server processes them in the following sequence: FROM, WHERE, and SELECT.

4) Sorting rows using the ORDER BY clause

To sort rows in a result set based, you use the ORDER BY clause. For example, the following query uses the ORDER BY clause to sort customers by their first names in ascending order.

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

Output:

When the SELECT statement includes the FROM, WHERE, and ORDER BY clause, SQL Server processes them in the following order: 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 order: 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 uses 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.