Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » MiscellaneousRSS Feeds

Operators Allowed in the WHERE Clause with examples

Posted By: Sarita Patel     Category: SQL Server     Views: 5121

This article explains operators allowed in where clause with different examples.

With the WHERE clause, the following operators can be used:

Operator

Description

=

Equal

<> 

Not equal

> 

Greater than

< 

Less than

>=

Greater than or equal

<=

Less than or equal

BETWEEN

Between an inclusive range

LIKE

Search for a pattern

IN

If you know the exact value you want to return for at least one of the columns

Note: In some versions of SQL the <> operator may be written as !=

Examples Operators Allowed in the WHERE Clause :

Example 1 : Filtering records having unit price equal to $20.00

SELECT ProductID, ProductName, UnitPrice 
FROM products
WHERE unitprice = 20.00

Output

ProductID  ProductName  UnitPrice
49              Maxilaku         20.00


Example 2 : Filtering records of unit price not equal to $20.00

SELECT ProductID, ProductName, UnitPrice 
FROM products
WHERE unitprice <> 20.00

Output

ProductID       ProductName       UnitPrice
1                        Chai                   18.00
2                        Chang                19.00
3                        Aniseed Syrup    10.00


Example 3 : Updating records having unit price greater than $20.00 and less than $25.00

UPDATE products
SET discontinued = 1
WHERE unitprice > 20.00
AND unitprice < 25.00
 
Example 4 : Deleting records having unit price "grater than and equal" to $25.90
and "less than and equal" to $26.00

DELETE FROM products 
WHERE unitprice >= 25.90 
AND unitprice <= 26.00

Example 5 : Filtering records of unit price between $18 to $20

SELECT  ProductID, ProductName, UnitPrice
FROM products 
WHERE unitprice BETWEEN 18 AND 20 

Output

ProductID          ProductName           UnitPrice
1                        Chai                         18.00
2                        Chang                      19.00
35                      Steeleye Stout           18.00
36                      Inlagd Sill                  19.00
39                      Chartreuse verte        18.00
40                      Boston Crab Meat     18.40
44                      Gula Malacca            19.45
49                      Maxilaku                   20.00
57                      Ravioli Angelo           19.50

Example 6 : Updating records having product name starting from 'a' using like operator. 

UPDATE products 
SET discontinued = 0 
WHERE productname LIKE 'a%' 

Example 7 : Updating records having product price 15, 18, 20 using in operator. 

UPDATE products 
SET reorderlevel = 10 
WHERE unitprice IN (15,18,20) 

  
Share: 


Didn't find what you were looking for? Find more on Operators Allowed in the WHERE Clause with examples Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of Operators Allowed in the WHERE Clause with examples is from United States.
 
View All Articles

 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!