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:
- 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. - 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.