How to Write a SQL SELECT Statement
By: Jim Evans | Updated: 2022-05-18 | Comments | Related: More > TSQL
I'm new to T-SQL and I want to know different ways to query data from a SQL Database. How do I write a SELECT statement to retrieve data from a SQL database table? I reviewed the article on "How to Connect to a SQL Database". Now I'm ready to write a SELECT statement and learn SQL commands.
In this SQL tutorial I'll explain and show examples of basic SQL SELECT statements for retrieving data from a database table.
First – Some Background Information
In a SQL Database, table names are referred to by a 2 part name including SchemaName.TableName. Tables belong to a schema, dbo being the default schema. Often schemas are used to organize the objects in a database. Typically, the schema name is the default schema "dbo" which stands for database owner.
Often you can reference a table by the name only like Customer vs dbo.Customer or in the examples below SalesLT.Customer where the schema name is SalesLT. You may see square brackets  around database object names, which are only required if the name has spaces or special character, else the brackets are optional. Keeping these basic rules in mind let's look at some examples.
The query examples in this article will reference tables in Microsoft's free sample database "AdventureWorksLT".
Example 1 – SELECT Basic Syntax
The example below has the minimum two clauses required: the SELECT which lists the columns and the FROM which specifies the table or tables you will pulling data back from.
--1) Basic SELECT returning All records and All columns SELECT * FROM [SalesLT].[Customer];
Quick syntax notes about the above query:
- Two dashes "--" make a line comment, ignored when running queries, but great for adding notes to your query scripts.
- The asterisk "*" in SQL syntax means return all columns!
- The ";" is a statement terminator. It is optional but in a future release of SQL it will be required!
Example 2 – SELECT with Specific Columns
This example is like the first, though rather than include all column (denoted by *), I list just 4 column names.
--2) SELECT returning All records but just a few specified columns SELECT [CustomerID],[Title],[LastName],[CompanyName] FROM [SalesLT].[Customer];
Example 3 – SELECT with Filtering Results using WHERE Clause
In this example a WHERE clause is used to filter the results. I will use the WHERE clause to only return rows for which CustomerID is less than or equal to 10. Normally a in the WHERE clause you will include a Column from the table in the FROM, an operator, and a condition. Common operators are =, <, >, <=, >=, Between, IN.
--3) SELECT statement with a simple WHERE condition filtering the results to only records with a CustomerID <= 10 SELECT [CustomerID],[Title],[LastName],[CompanyName] FROM [SalesLT].[Customer] WHERE CustomerID <= 10;
*Note: the data type of the condition should match that of the column!
Example 4 – SELECT Sorting the Results with ORDER BY
In this example I add "ORDER BY" to control the sort order of the results. ASC means ascending order; DESC means descending.
--4) SELECT Statement with an ORDER BY clause returning Sorted Results by CompanyName SELECT [CustomerID],[Title],[LastName],[CompanyName] FROM [SalesLT].[Customer] WHERE CustomerID < 10 ORDER BY CompanyName ASC;
Example 5 – SELECT Using IN and BETWEEN Operators
This example demonstrates the use of the IN list and the BETWEEN operators. Also included are the different sort options ascending and descending.
--5a) SELECT Statement IN list WHERE clause, Sorted Descending SELECT [CustomerID],[Title],[LastName],[CompanyName] FROM [SalesLT].[Customer] WHERE CustomerID in (1,3,10) ORDER BY CompanyName DESC; --5b) SELECT Statement BETWEEN WHERE clause, Sorted Ascending SELECT [CustomerID],[Title],[LastName],[CompanyName] FROM [SalesLT].[Customer] WHERE CustomerID BETWEEN 10 AND 20 ORDER BY CompanyName ASC;
Example 6 – SELECT n Rows
In this example we will limit the number of rows returned using the TOP n keyword. Combining this with and Order BY column ASCENDING (ASC) or DESENDING (DESC) will give to top n or bottom n records. With n being a number or integer variable.
--6a) SELECT Top 10 SELECT TOP 10 [CustomerID],[Title],[LastName],[CompanyName] FROM [SalesLT].[Customer] ORDER BY [LastName] ASC --6b) SELECT TOP n DESC with n as a Variable. DECLARE @Rows INT = 10 SELECT TOP (@Rows) [CustomerID],[Title],[LastName],[CompanyName] FROM [SalesLT].[Customer] ORDER BY [LastName] DESC
Example 7 – SELECT Distinct
This example returns a unique list of CompanyName.
--7) SELECT DISTINCT SELECT DISTINCT [CompanyName] FROM [SalesLT].[Customer];
Example 8 – SELECT with GROUP BY
GROUP BY can also return distinct values though typically used with aggregate functions: COUNT, SUM, AVG, MIN, MAX and many others.
--8a) SELECT returning distinct with Group By SELECT [CompanyName] FROM [SalesLT].[Customer] GROUP BY [CompanyName] --8b) Group BY with Aggregate function COUNT. The Column name must be in the GROUP BY clause. SELECT [CompanyName], COUNT(*) FROM [SalesLT].[Customer] GROUP BY [CompanyName] ORDER BY COUNT(*) DESC;
Example 9 – SELECT with Inner Join
This example shows an INNER JOIN to combine results from two tables.
--9) SELECT returning All Customer and their SalesOrders SELECT c.[CustomerID], c.[LastName], c.[CompanyName], s.PurchaseOrderNumber, s.OrderDate FROM [SalesLT].[Customer] c INNER JOIN [SalesLT].[SalesOrderHeader] s ON s.CustomerID = c.CustomerID;
In this SQL tutorial we introduced the basic SELECT statement. These examples should help to get you started. Look for many other MSSQLTip articles to learn more advance tips on SQL Queries.
- Look for tips to learn how to write basic Insert, Update and Delete statements.
- How to Connect to a SQL Database
- Learn more about SQL Server SELECT Examples
- Learn about multiple table Selects: Getting Started with SQL INNER JOIN
- Read SQL JOIN Types with Examples
- Read SQL RIGHT JOIN Examples
- Read Creating a table using the SQL SELECT INTO clause - Part 1
- Read Introduction to Subqueries in SQL Server
- Read Differences between Delete and Truncate in SQL Server
- Read SQL Server DROP TABLE IF EXISTS Examples
About the author
View all my tips
Article Last Updated: 2022-05-18