In this SQL Server tutorial, you’ll learn the basics of querying the data from the SQL Server database table using SELECT Statement with its syntax and various examples of how to use it.
In SQL Server, the database tables are the objects that store all the data which are logically organized into rows and columns similar to Excel worksheet. Each column in the table represents the name of the field whereas each row represents the record in the table. For example, let’s take the Person table in the AdventureWorks database. This table contains the customer data like FirstName, MiddleName, Last name, etc.
SQL Server uses schemas to logically group tables and database objects. By default, everything goes under the dbo schema. In the sample AdventureWorks database that we are using, we have a different schema to separate the functionality. Eg: sales, production, Person, Human Resources, etc.
To query the data from a table from a database in SQL Server, you will need to use the SELECT statement. The SELECT statement retrieves records from one or more tables from a SQL Server database.
Syntax
SELECT [ ALL| DISTINCT ] [ TOP (top_value) [ PERCENT ] [ WITH TIES ] ] expressions FROM tables [WHERE conditions] [GROUP BY expressions] [HAVING condition] [ORDER BY expression [ ASC | DESC ]];
Parameters
- ALL
- This is an optional parameter that returns all matching rows.
- DISTINCT
- This is an optional parameter. It ensures that the duplicate results are removed and only the unique results are provided.
- TOP
- This is an optional parameter. When this is specified, it will return the specified number of rows in the result set. For example, if you want to return the first 500 records from the result set, you’ll need to specify TOP(500).
- PERFECT
- This is an optional parameter. When this is specified, it will return the top rows based on a percentage of the total result set. For example, TOP(50) PERCENT will return the TOP 50 perfect of the records.
- WITH TIES
- This is an optional parameter. When this is specified, it returns the rows that are tied in the last place within the limited result set.
- expressions
- This is mandatory. The expressions here are the columns, fields, or calculations that you wish to return as part of the result set. If you want to return all the columns from the table, you can use “*” instead.
- WHERE <conditions>
- This is optional and is primarily used to filter the records based on the specified conditions.
- GROUP BY <expressions>
- This is an optional argument that collects data and groups them by one or more columns.
- HAVING clause
- This is an optional parameter and is mainly used in combination with the GROUP BY clause to return the results from the group by clause tat matches the conditions specified in the HAVING clause.
- ORDER BY expression
- This is an optional parameter that sorts the records in your result set. You can specify the columns and the sort order (ASC for ascending and DESC for descending order)
For this example, let’s use the Person table defined under the schema “Person” from the AdventureWorks database.
Example 1 – Select all the columns from a single table
SELECT * FROM [Person].[Person]
In this SELECT statement example, we have used * to indicate that we want to select all the fields/columns from the Person Table under the Person schema. The result of the above query is the result set. * is the shorthand to save us some time so that we don have to specify all the columns manually.
SELECT * statement is a very useful one that gets all the columns along with the data from the table but when using the SELECT statement, always look at specifying the columns that you need to be retrieved instead of specifying *.
Example 2 – Select some columns from a single table
SELECT FirstName,LastName FROM [Person].[Person]
You can specify individual fields from the table in the SQL Server SELECT statement instead of all the fields. The above query retrieves the first name and last name of all customers.
Example 3 – Select and Filter the Result using WHERE
If you want to filter the result set, you can specify one or more conditions by using the WHERE clause as shown below.
SELECT * FROM [Person].[Person] WHERE FirstName = 'ken'
In the above SQL statement, the query filters all the Person with the FirstName “ken”
Example 4 – Select and Sort result using ORDER BY
SELECT * FROM [Person].[Person] ORDER BY FirstName DESC
In the above SQL Select query, the ORDER BY clause sorts the result set by the FirstName in descending order.
Example 5 – Select TOP X Records
SELECT TOP 10 * FROM [Person].[Person] ORDER BY FirstName DESC
In the above SQL query, the SELECT statement will return the first 10 records from the Person table after ordering the results by descending order. The other results will be ignored.
Example 6 – Select TOP PERCENT Records
SELECT TOP 10 PERCENT * FROM [Person].[Person] ORDER BY FirstName DESC
In the above SQL query, the SELECT statement will return the first 10% of the records from the result set from the Person table. The other 90% of the result set will not be returned in the above query.
Example 7 – Select columns from multiple tables
SELECT TOP 10 * FROM [Person].[Person] as PER INNER JOIN Person.PersonPhone AS PERPH ON PER.BusinessEntityID = PER.BusinessEntityID ORDER BY FirstName DESC
You can use the SELECT statement to return records from multiple tables. You can apply any of the joins like inner join or outer join to get the results.
In the above query, the SELECT query returns the top 10 results set from two tables – Person and PersonPhone and displays all the columns from both of these tables. The result is sorted by FirstName in descending order.
Example 8 – Select & GROUP BY
SELECT PersonType, COUNT(1) FROM [Person].[Person] GROUP BY PersonType
You can group the records using the GROUP BY clause in SQL Server. In the above example, the select statement returns all the records from the Person table with the number people in each persontype.
Example 9 – Select & HAVING
You can use the HAVING clause to filter groups based on conditions.
SELECT PersonType, COUNT(1) FROM [Person].[Person] GROUP BY PersonType HAVING COUNT(1) > 10000
In the above example, the SELECT query returns the count of people with persontype when there are more than 10000 people of that type.
