Learn SQL SELECT with Sample Queries

By: Jim Evans | Updated: 2022-05-18 | Comments | Related: > TSQL

Problem

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.

Solution

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;

Wrap Up

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.

Next Steps

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

Jim Evans is an IT Manager currently for Crowe who has managed DBA, Developer, BI and Data Management teams for over 20 years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-05-18

How to Write a SQL SELECT Statement