SQL Server SELECT queries to select and filter data

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.

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.

select data from one column with a T-SQL query in SSMS
select data from one column with a T-SQL query in SSMS

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;
SQL Server SELECT query with all columns in the result
SQL Server SELECT query with all columns in the result

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;
SQL Server query to select data from the table using the OR operator
SQL Server query to select data from the table using the OR operator

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:

  • January
  • February
  • March
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 :

  1. The exact number of columns used
  2. The name of the columns
  3. 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.

More T-SQL tutorials on how to manage tables

Be the first to comment

Leave a Reply

Your email address will not be published.


*