Tutorial Gateway

SQL WHERE Clause

by

The SQL WHERE Clause is used to restrict the number of rows (or records) returned by the SELECT Statement. The SELECT Statement will return the records only If the condition specified after the WHERE keyword is TRUE.

For instance, we want to order shoes on the Amazon website. When we type shoes in the search bar it will show thousands of shoes but To select required shoe we have to use filters such as price range between 2000 to 2500 and brand = Nike or Adidas.

If we use the above filters it will only display the shoes that are matching the above requirements so that we can select easily. Let us see what happens internally (mean T-SQL Query against the Amazon Database). It will go something like this:

-- SQL Server WHERE Clause Example
SELECT [Product Name], [Size], [Brand], [Price], [Discount]
FROM [Products_table]
WHERE ([Product Name] = 'Shoes') AND 
      (Brand = 'Nike' OR 'Adidas') AND 
      ([Price] BETWEEN 2000 AND 2500)

SQL WHERE Syntax

The Syntax of a SELECT Statement and WHERE in SQL Server can be written as:

-- SQL Server WHERE Clause Syntax
SELECT [Column Names]
FROM [Source]
WHERE [Conditions]
  • Columns: It allows us to choose the number of columns from the tables. It may be One or more.
  • Source: One or more tables present in the Database. JOINS are used to join multiple tables.
  • Conditions: Here we have to provide the filters or conditions. If the condition is TRUE then only SELECT Statement will return the records.

In this article, we will show you, How to use the SQL WHERE to filter the data before extracting it using the SELECT statement.

We are going to use the below-shown data to explain the WHERE in SQL Server with an example.

SQL WHERE Clause 1

SQL WHERE Single Condition

In this example, we are going to use single condition in the WHERE Clause.

-- SQL Server WHERE Example
USE [SQL Tutorial]
GO
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [Occupation] = 'Professional'

Above statement will retrieve above-mentioned records where the occupation is exactly equal to Professional

OUTPUT

SQL WHERE Clause 2

SQL Where Multiple Conditions Example

In this Sql Server where example, we are going to use Multiple Conditions in the WHERE Clause. Below query returns, all the Customer records where the occupation is equal to either Professional or Management.

-- SQL Server WHERE Clause Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [Occupation] = 'Professional' OR
	[Occupation] = 'Management'

OUTPUT

SQL WHERE Clause 3

SQL Where on General Expressions

Instead of testing conditions against Column Names, Sql Server Where Clause allows us to test general conditions.

For instance, below condition is checking 1 is exactly equal to 2, which is False. So SELECT statement will not return any record.

-- SQL Server WHERE Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE 1 = 2

OUTPUT

SQL WHERE Clause 4

Let us change the condition to (1 = 1). Here the condition is TRUE so it is displaying all the records present in the Customers Table

-- SQL Server WHERE Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE 1 = 1

OUTPUT

SQL WHERE Clause 5

NOTE: Do not use ALIAS Column Names in the WHERE Condition. Because WHERE Clause will execute first and then SELECT Statement will select the Columns. So, WHERE Clause does not understand the ALIAS Columns declared in the SELECT Statement.

SQL Where Subquery

In SQL Server, you can use Subqueries in the Where clause to check the column against the expression. In this example, we are using where clause to display the records whose yearly income is greater than or equal to Average Income.

Here, we used AVG to find the average of yearly income column.

-- SQL Server WHERE Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [YearlyIncome] >= (SELECT AVG([YearlyIncome]) 
			   FROM [Customer])

OUTPUT

SQL WHERE Clause 6

SQL Where Order By Clause

We can also use Where clause along with the Order By Clause. Here, Order By clause will sort those query result based on the yearly income in descending order.

-- SQL Server WHERE Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [YearlyIncome] >= (SELECT AVG([YearlyIncome]) 
				FROM [Customer])
  ORDER BY [YearlyIncome] DESC

OUTPUT

SQL WHERE Clause 7

SQL Where Update Example

The Sql Server Where clause does not about restrict the records selected by the Select Statement. It is very useful, in fact, mandatory to use this inside an Update Statement.

Below query add 132500 to Yearly income column, and multiply Sales by 2 for all the customers whose Occupation is either Management or professional

-- SQL Server WHERE Example
UPDATE [Customer]
	SET	[YearlyIncome] = [YearlyIncome] + 132500
      ,[Sales] = [Sales] * 2
  FROM [Customer]
  WHERE [Occupation] = 'Management' OR 
		[Occupation] = 'Professional'

OUTPUT

SQL WHERE Clause 8

Now, let me show you the Customer table to check whether we successfully updated the records or not

SQL WHERE Clause 9

SQL Where Delete Example

When you are deleting records, sql server where clause will help you to restrict the number of records you want to delete. This is very useful in Delete Statement.

Below query delete all the customer records whose Occupation is Clerical

DELETE FROM [Customer]
  WHERE [Occupation] = 'Clerical'
GO

SELECT [EmpID], [FirstName], [LastName], [Education]
		,[Occupation], [YearlyIncome], [Sales]
  FROM [Customer]

OUTPUT

SQL WHERE Clause 10

SQL Where Like Example

You can use Like Operator along with SQL Where Clause to perform Wildcard searches against the Table.

Below code writes all the records from customer table whose Occupation ends with l.

-- SQL Server WHERE Example
SELECT [EmpID], [FirstName], [LastName], [Education]
		,[Occupation], [YearlyIncome], [Sales]
  FROM [Customer]
  WHERE [Occupation] LIKE '%l'

OUTPUT

SQL WHERE Clause 11

Placed Under: SQL