SQL AND & OR Operators

In SQL Server, The Logical Operators such as AND & OR Operators will be used in the WHERE Clause to apply more filters on the records returned by the SELECT Statement.

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 pick a shoe, we have to use various filters such as price range between 2000 to 2500 and brand = Nike or Adidas and Size = 9. If we use the above filters, it displays the shoes that are matching the above requirements, so that we can select easily. Let us see what happens internally (Query against the Amazon DB). SQL Logical AND, OR operator query will go something like this:

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

From the above Query, observe that we used multiple filters with the help of SQL Logical AND, OR operators

Let us see how to use the Logical Operators (Such as SQL AND & OR) in the WHERE clause to filter the data before extracting it using the SELECT statement. We are going to use the below-shown data to explain the AND, OR Operators in SQL Server with example.

SQL Logical Operators

SQL AND & OR Operators Examples

The following are the list of And and OR operator examples

SQL AND Operator

The SQL Server AND Operator used to test multiple conditions in the WHERE Clause. If all the conditions are TRUE, then only Select Statement will display the records.

-- SQL Server AND Operator Example
SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
 FROM [Customer]
 WHERE [Education] = 'Bachelors' AND 
       [Occupation] = 'Professional'

The above statement will retrieve all Customers available in a Customer table whose Education is exactly equal to bachelor and occupation exactly equaled to Professional.

SQL AND Operator 1

Let us apply three conditions using SQL AND Operator

-- SQL Server AND Operator Example
SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
 FROM [Customer]
 WHERE [Education] = 'Bachelors' AND 
       [Occupation] = 'Professional' AND
       [YearlyIncome] = 70000

The above statement selects all Customer records present in a Customer table whose Education is exactly equal to Bachelor’s and occupation is precisely equal to Professional. Also, his yearly income is precisely equal to 70000.

SQL AND Operator 2

SQL OR Operator

The SQL Server OR Operator is used to test multiple conditions in the WHERE Clause. If any of the given conditions is TRUE, then it displays the records.

Let us apply Filters on Single Column (ie, Occupation) Using OR Operator

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

The earlier statement gets all the Customers present in Customers table whose occupation is equal to either Management or Professional.

SQL OR Operator 1

Let us apply Filters on Multiple Column Using OR Operator

-- SQL Server OR Operator Example
SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
FROM [Customer]
WHERE [Occupation] = 'Professional' OR
      [YearlyIncome] >= 60000

The earlier statement will retrieve all the Customers from the Customers table If their Occupation is equal to Professional. Or if the Yearly Income is greater than or equal to 60000

SQL OR Operator 2

From the above screenshot observe that, although we asked for ‘Professional’, it is displaying Management and Skilled Manual customers because the Yearly Income is greater than or equal to 60000

Combining SQL AND & OR Operators

Usually, we can combine SQL AND Operators and OR Operators to apply complex filters. However, you have to use parentheses.

NOTE: If we forget the parentheses, then we will end up with the wrong results. Please be careful

-- SQL Server AND, OR Operator Example
SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
FROM [Customer]
WHERE ([Occupation] = 'Professional' OR 
         [Occupation] = 'Management') AND
      [YearlyIncome] >= 70000

The above statement will retrieve all the Customers present in the Customers table whose occupation is equal to either Management or Professional. Moreover, his Yearly Income is greater than or equal to 70000.

Combining SQL AND & OR Operators 1

Let us change the AND, Or Operators position in the query

-- SQL Server AND, OR Operator Example
SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
FROM [Customer]
WHERE ([Occupation] = 'Professional' AND 
           [Education] = 'Bachelors') OR
      [YearlyIncome] >= 60000
Combining SQL AND & OR Operators 2