Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs

SQL WHERE Clause

by suresh

The SQL WHERE Clause is used to restrict the number of rows (or records) returned by the SELECT Statement. The SELECT statement returns the records only If the condition specified after the SQL 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. However, to select the 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 (Query against the Amazon Database). It uses SQL Where clause, and 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]

From the above where syntax

  • Columns: It allows us to choose the number of columns from the tables. It may be one or more.
  • Source: One or more tables from the Database. JOINS are used to join multiple tables.
  • Conditions: Here, we have to provide filters or conditions. If the condition is TRUE, then only the SELECT Statement returns the records.

We use the below-shown data to explain the SQL Server WHERE Clause to filter the data before extracting it using the SELECT statement.

SQL WHERE Clause 1

SQL WHERE Single Condition

Using a single condition inside the WHERE Clause in SQL Server.

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

It will retrieve records mentioned above where the Occupation is exactly equal to Professional

SQL WHERE Clause 2

SQL Where Multiple Conditions Example

This Sql Server where example use Multiple Conditions in the WHERE Clause. The 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'
SQL WHERE Clause 3

SQL Server Where on General Expressions

Instead of testing conditions against Column Names, 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
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
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, SQL WHERE Clause does not understand the ALIAS Columns declared in the SELECT Statement.

SQL Where Subquery

In SQL Server, we can use Subqueries in the Where clause to check the column against the expression.

Here, we use SQL 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])
SQL WHERE Clause 6

SQL Where Order By Clause

We can also use SQL Where clause along with the Order By Clause. The Order By clause will sort those query result based on the 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
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 useful, in fact, mandatory to use this inside an Update Statement.

The 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'
SQL WHERE Clause 8

Now, let us see the Customer table to check whether we successfully updated the records or not

SQL WHERE Clause 9

Where Delete Example

When we are deleting records, SQL Server where Clause will help us to restrict the number of records we want to delete. It is useful in Delete Statement.

The 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]
SQL WHERE Clause 10

SQL Where Like Example

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

Below code writes all the records from the 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'
SQL WHERE Clause 11

Placed Under: SQL

  • Install SQL Server
  • Install SQL Management Studio
  • Uninstall Management Studio
  • Install AdventureWorks Database
  • SQL Management Studio Intro
  • Connect SQL with sqlcmd utility
  • SQL Attach Database
  • SQL Detach Database
  • SQL Restore Database
  • Restore Database using BAK
  • SQL Rename Database with Files
  • Get SQL Database Names
  • SQL Create Table
  • SQL Rename Table
  • SQL Alter Table
  • SQL Add Column
  • SQL Rename Column
  • Get SQL Table Names in a DB
  • Find SQL Table Dependencies
  • Rename SQL Table & Column
  • SQL Global & Local Temp Table
  • SQL Table Variable
  • SQL Derived Table
  • SQL DATALENGTH
  • SQL Data Types
  • DML, DDL, DCL & TCL Cmds
  • SQL Query Builder
  • SQL ALIAS
  • SQL SELECT Statement
  • SQL SELECT DISTINCT
  • SQL SELECT INTO Statement
  • SQL INSERT Statement
  • SQL INSERT INTO SELECT
  • SQL BULK INSERT or BCP
  • SQL UPDATE Statement
  • SQL UPDATE from SELECT
  • SQL DELETE Statement
  • SQL TRUNCATE Table
  • SQL CASE Statement
  • SQL MERGE Statement
  • SQL Subquery
  • SQL CTE
  • SQL PIVOT
  • SQL UNPIVOT
  • SQL Clauses Examples
  • SQL TOP Clause
  • SQL WHERE Clause
  • SQL ORDER BY Clause
  • SQL GROUP BY Clause
  • SQL HAVING Clause
  • SQL Primary Key
  • SQL Foreign Key
  • SQL Referential Integrity
  • SQL Check Constraint
  • SQL Unique Constraint
  • SQL Default Constraint
  • SQL Clustered Index
  • SQL Non Clustered Index
  • SQL Filtered Indexes
  • SQL COALESCE Function
  • SQL IS NOT NULL
  • SQL IS NULL Function
  • SQL ISNULL
  • SQL JOINS
  • SQL CROSS JOIN
  • SQL FULL JOIN
  • SQL SELF JOIN
  • SQL Outer Joins
  • SQL Cross Join Vs Inner Join
  • SQL LEFT JOIN
  • SQL RIGHT JOIN
  • SQL AND & OR Operators
  • SQL Arithmetic Operators
  • SQL BETWEEN Operator
  • SQL Comparison Operators
  • SQL LIKE
  • SQL EXCEPT
  • SQL EXISTS Operator
  • SQL NOT EXISTS Operator
  • SQL INTERSECT
  • SQL IN Operator
  • SQL NOT IN Operator
  • SQL UNION
  • SQL UNION ALL
  • SQL IF ELSE
  • SQL ELSE IF
  • SQL WHILE LOOP
  • SQL Nested While Loop
  • SQL BREAK Statement
  • SQL CONTINUE Statement
  • SQL GOTO Statement
  • SQL IIF Function
  • SQL CHOOSE Function
  • SQL Change Data Capture
  • SQL Table Partitioning
  • SQL Table Partition using SSMS
  • SQL TRY CATCH
  • SQL VIEWS
  • SQL User Defined Functions
  • SQL Alter User Defined Functions
  • SQL Stored Procedure Intro
  • Useful System Stored Procedures
  • SQL SELECT Stored Procedure
  • SQL INSERT Stored Procedure
  • SQL UPDATE Stored Procedure
  • Stored Procedure Return Values
  • Stored Procedure Output Params
  • Stored Procedure Input Params
  • Insert SP result into Temp Table
  • SQL Triggers Introduction
  • SQL AFTER INSERT Triggers
  • SQL AFTER UPDATE Triggers
  • SQL AFTER DELETE Triggers
  • SQL INSTEAD OF INSERT
  • SQL INSTEAD OF UPDATE
  • SQL INSTEAD OF DELETE
  • SQL STATIC CURSOR
  • SQL DYNAMIC CURSOR
  • SQL FORWARD_ONLY Cursor
  • SQL FAST_FORWARD CURSOR
  • SQL KEYSET CURSOR
  • SQL TRANSACTIONS
  • SQL Nested Transactions
  • SQL ACID Properties
  • Create SQL Windows Login
  • Create SQL Server Login
  • SQL Server Login Error
  • Create SQL Server Roles
  • SQL Maintenance Plan
  • Backup SQL Database
  • SQL Ranking Functions Intro
  • SQL RANK Function
  • SQL PERCENT_RANK Function
  • SQL DENSE_RANK Function
  • SQL NTILE Function
  • SQL ROW_NUMBER
  • SQL Aggregate Functions
  • SQL Date Functions
  • SQL Mathematical Functions
  • SQL String Functions
  • SQL CAST Function
  • SQL TRY CAST
  • SQL CONVERT
  • SQL TRY CONVERT
  • SQL PARSE Function
  • SQL TRY_PARSE Function
  • SQL Calculate Running Total
  • SQL Find Nth Highest Salary
  • SQL Reverse String
  • SQL FOR XML PATH
  • SQL FOR XML AUTO
  • SQL FOR XML RAW

Copyright © 2021· All Rights Reserved.
About | Contact | Privacy Policy