Example queries with the SQL Server SELECT function to select, filter and display data.
T-SQL tutorial to learn how to use the SQL Server select queries to display and filter data from a table. This is the basic query because SQL, for Structured Query Language, is primarily designed to query and display data from a table. The query is of the type DML for Data Manipulation Language.
Table of Contents
1. Examples of basic SQL Server SELECT queries
The SELECT command is used to display and read data. The SELECT query is therefore the most widely used and indispensable in SQL. Furthermore, it allows you to display a single column or all columns in a single SELECT query. This means that it is possible to filter in two different ways:
- Horizontal filter: Select one or more columns
- Vertical filter: filtering the data with conditions and the WHERE clause for example
1.1 Create the table used in the SELECT queries
First, before running the selection commands, create the example table of sales by month with this script.
-- Test if the table already exists -- If it doaes then delete the table IF EXISTS( SELECT 1 FROM sys.objects WHERE object_id = object_id(N'[dbo].[SALES]') AND type in (N'U') ) DROP TABLE [dbo].[Sales] GO -- Create the example table to store SALES CREATE TABLE [dbo].[Sales] ( [MonthName] nvarchar(20), [Amount] numeric(8) ) GO
1.2 Insert data into the sales table
Second step, insert rows with monthly sales amounts for the 12 months of the year.
-- Inserting the Sales data for our example INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'January', 1000); INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'February', 2000); INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'March', 3000); INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'April', 1000); INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'May', 2000); INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'June', 3000); INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'July', 1000); INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'August', 2000); INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'September', 3000); INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'October', 1000); INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'November', 2000); INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'December', 3000); GO -- Select results for checking SELECT * FROM dbo.SALES;
1.3 Single column selection query
In the example below, select only the MONTH column from the sales table.
SELECT [MonthName] FROM dbo.SALES;
Here is a SQL Server SELECT query to display only the month from the sales table.
1.4 SELECT STAR FROM query to display all columns
On the other hand, in this example, the query displays all the columns of the table, i.e. MONTH and AMOUNT.
SELECT * FROM dbo.SALES;
2. SQL Server SELECT queries with the WHERE clause
The WHERE clause allows you to filter the data by adding one or more conditions and thus display only the rows of the table that match the condition. The WHERE clause in SQL is certainly the most used LMD clause in the database field. LMD stands for Data Manipulation Language, which is the language used to manipulate data and not the objects themselves.
2.1 Selection with a filter on the Month column
To display the month name and amount of the row whose month name is exactly January.
SELECT [MonthName], [Amount] FROM dbo.SALES WHERE [MonthName] = 'January';
2.2 Selection query with the WHERE clause and an OR operator
The OR operator is a logical OR. To display the months whose name is January or February. Note that the OR clause allows you to mix conditions on different columns and thus filter the data in the table precisely. Either in an inclusive or more exclusive way.
SELECT [MonthName], [Amount] FROM dbo.SALES WHERE [MonthName] = 'January' OR [MonthName] = 'February' OR [Amount] > 1000;
2.3 MS SQL selection query with the IN operator
Similarly, to display all the rows in the table whose month is equal to a specific value, use the SQL IN operator. For example, this query will display the first three months of the year:
SELECT [MonthName], [Amount] FROM dbo.SALES WHERE [MonthName] IN ('January', 'February', 'March');
2.4 SQL query with WHERE clause and the different operator
To display a list of all customers whose name is different from MAMMADOU. This example query uses the star keyword: *. The SLECT * keyword displays all the columns of the table. The SELECT star keyword, also called SELECT STAR in English, or allows to display all the columns without naming them explicitly.
SELECT * FROM dbo.SALES WHERE [MonthName] <> 'December';
Note that it is good SQL Server practice not to use the SELECT * keyword. Especially in programs or scripts where the columns change. Indeed, if a column name changes then the program no longer recognizes the column. Unless the program manages the column names dynamically. It is therefore recommended to name all columns explicitly to control :
- The exact number of columns used
- The name of the columns
- And the order of the columns
Finally, this IT tutorial explains and breaks down simple Select SQL Server queries. I.e. queries useful to perform basic data selections and filters. To go further, learn how to manage tables such as creating a table or modifying a column with the Alter Column command.