The SQL SELECT statement is the workhorse of data access. It is the standard way to retrieve data from a SQL database. In this tutorial, you will learn the basics of the SQL Server SELECT statement, focusing on how to query against a single table.
SQL Server SELECT statement
A database is a place that stores all of your application's data. It's kind of like a filing cabinet where you store documents; however, with a database, you don't have to dig around through the filing cabinet to find the document that you need. Instead, the database is designed so that you can use keywords to quickly search for any piece of information that you need by going directly to its location. This makes things much easier and more efficient!
Each row represents an instance of one specific record, and each column is a field that has that same value throughout the entire object. For example, the customer table contains all of your company's customer records, which contain fields such as customer identification number, first name, last name, phone number, email address, and information about their transactions and interactions with your business.
To select data from the database, you use the SELECT statement. The following illustrates the most basic form of the SELECT statement:
SELECT
column1, column2, ....ColumnN
FROM
database_name.schema_name.table_name;Firstly, in the Select clause, specify the list of columns that we want to query from the table. You need to separate multiple columns by the comma.
The FROM clause requires the following values.
database_name: It is an optional field. If you do not specify any database, the script runs in the current database context.
schema_name: SQL Server organizes tables into the logical schema. We can have multiple schemas in a single database. It is also an optional field. If you do not specify the schema, it uses the dbo schema.
table_name: Specify the database table from which you want to retrieve data. It is a mandatory field
Let's explore the SQL Server Select statement using examples.
Example 1: Retrieve all columns from a table
The following query returns all columns and rows from the Customer table in [SalesLT] schema.
SELECT *
FROM [SalesLT].[Customer]Example 2: Retrieve specific columns from a table
In most cases, we require only specific fields in our output. For example, let's say we require the first and last names of the customers.
In this case, you need to specify the column names, separated by a comma, as per the following query.
SELECT FirstName, LastName
FROM [SalesLT].[Customer]As shown below, our output contains only two columns - FirstName and LastName as specified in the select statement.
Example 3: Filter rows in SQL Server Select statement
Suppose your table has millions of rows and you want to retrieve data that satisfy a specific condition. In this case, it is not wise to retrieve all millions of rows as it will take a lot of system resources and time.
To filter records in the select statements, we use the WHERE clause and specify filter conditions.
For example, suppose we require customer records that belong to a company called - A Bike Store.
Therefore, in the select statement, use the WHERE clause ( after FROM) and specify the column name and matching value to return the required result.
In this case, we retrieve three columns - FirstName, LastName and CompanyName from the [SalesLt].[Customer] table.
SELECT FirstName, LastName, CompanyName
FROM [SalesLT].[Customer]
WHERE CompanyName='A Bike Store'The following figure returns only two records satisfying the where clause condition.
Example 3: Sort data in SQL Server Select statement
You can sort query results (data) in ascending or descending order using the ORDER BY Clause following by the column name on which the sorting needs to be done.
The default sorting order is ascending. Therefore, if we do not specify the ASC keyword, SQL Server automatically sorts data in default order.
The following select statement sorts result on the [CustomerID] column in ascending order.
SELECT CustomerID, FirstName, LastName, CompanyName
FROM [SalesLT].[Customer]
ORDER BY CustomerID
To sort results in descending order, specify the DESC keyword.
SELECT CustomerID, FirstName, LastName, CompanyName
FROM [SalesLT].[Customer]
ORDER BY CustomerID DESCAs shown below, the result shows the customer records (as per Customer IDs)in descending order.
Example 4: Get count of records using SQL Server Select statement
Suppose you do not require data in the output of the select statement, The requirement is to check the count of records in the table.
To get the count, we use the COUNT(*) operator as shown below.
SELECT Count(*)
FROM [SalesLT].[Customer]As shown below, the customer table has 847 rows.
In the above select statement, you do not get any column name. The output shows - No Column Name.
You can specify the required column name followed by the AS clause. For example, the below query shows column name [RecordCount].
SELECT Count(*) AS RecordCount
FROM [SalesLT].[SalesOrderDetail]
Example 5: Get top N number of records using SQL Server Select statement
Suppose instead of getting all records from the select statement output, you want to get Top 'N' records. You can specify the TOP(n) clause in the select statement and get only those specific number of rows.
The below SQL statement returns the top 5 records from the [SalesOrderDetail] table.
SELECT TOP(5) *
FROM [SalesLT].[SalesOrderDetail]
If you run the above query, it might return a different set of records. It gives random records because we have specified any ORDER BY clause to sort data in a specific order.
SELECT TOP(5) *
FROM [SalesLT].[SalesOrderDetail]
ORDER BY UnitPrice
The order by clause ensures you get the same records on each query execution provided there is no change in the table records.
Example 5: Group records in SQL Server Select statement
You can group results on a specific column using the GROUP BY statement. For example, let's say, we want to retrieve the number of customers for each company name.
In this case, we can group results using the GROUP BY clause on the [CompanyName] column.
SELECT CompanyName, Count(*) AS NumberofRecords
FROM [SalesLT].[Customer]
GROUP BY CompanyNameAs shown below, the select statement returns a number of records for each company type.
You can filter records in the GROUP BY statement using the HAVING clause as well. For example, let's say, we want to get the company name and record count whose number of records is greater than 1.
SELECT CompanyName, Count(*) AS NumberofRecords
FROM [SalesLT].[Customer]
GROUP BY CompanyName
HAVING count(*)>1As shown below, the query returns the company name for those the number of records is greater than 1.
