SQL AND & OR Operators

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

SQL AND, OR Operators Example: 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 match the above requirements, so we can select them easily. Let us see what happens internally (Query against the Amazon DB). The SQL Server 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 AND & OR operators in the WHERE clause to filter the data before extracting it using the SELECT statement. We will use the below-shown data to explain the AND & OR Operators in SQL Server with an example.

SQL Server AND & OR Operators Examples

The following is the list of SQL logical And and OR examples

AND Operator

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

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 a bachelor’s and whose occupation is exactly equaled to a Professional.

Let us apply three conditions using the logical AND.

SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
 FROM [Customer]
 WHERE [Education] = 'Bachelors' AND 
       [Occupation] = 'Professional' AND
       [YearlyIncome] = 70000

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

SQL Server OR Operator

The OR Operator tests multiple conditions in the WHERE Clause. If any given conditions are TRUE, it displays the records.

Let us apply Filters on a Single Column (i.e., Occupation) Using OR Operator

SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
FROM [Customer]
WHERE [Occupation] = 'Management' OR
      [Occupation] = 'Professional'

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

Let us apply Filters on Multiple Column Using the logical OR.

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

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

SQL Combining AND, OR Operators

Usually, we can combine SQL AND 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

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 at the Customer table whose occupation is equal to either Management or Professional. Moreover, his Yearly Income is greater than or equal to 70000.

Let us change the SQL Server AND and Or operators positions in the query.

SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
FROM [Customer]
WHERE ([Occupation] = 'Professional' AND 
           [Education] = 'Bachelors') OR
      [YearlyIncome] >= 60000