Build SQL Queries with AND, OR, NOT Logical Operators

By: Joe Gavin | Updated: 2023-03-14 | Comments | Related: 1 | 2 | 3 | 4 | 5 | > TSQL

Problem

What are the AND, OR, and NOT operators in the SQL (Structured Query Language) programming language and how can I use them as a beginner SQL Developer?

Solution

AND, OR, and NOT are commonly used T-SQL Logical Operators that are used with a WHERE or HAVING clause to filter on more than one condition.

AND displays records where ALL the conditions specified are true
OR displays records where ANY of the conditions specified are true
NOT displays records where the condition(s) specified are NOT TRUE

This SQL tutorial will demonstrate examples of the three operators with T-SQL code that can be copied, pasted, and modified for your needs. Each example is in the following format:

Data we want in the form of a question
T-SQL query
Result set returned

The following SQL queries have been run in the AdventureWorks2019 sample SQL database. AdventureWorks2019 is a free relational database from Microsoft for a fictitious bicycle manufacturer called Adventure Works Cycles. If you want to recreate these examples, follow the steps in this tip, AdventureWorks Database Installation Steps, to show you how to download and install it with SQL scripts or restore it from a database backup.

After the database has been installed, you can change the context as follows to use it for the examples.

USE [AdventureWorks2019];
GO

SQL AND Operator

Syntax: boolean_expression AND boolean_expression

The AND operator returns records if all of the conditions specified in the WHERE Clause are true for a SELECT statement.

Example 1: What employees have a pay frequency of 1 AND the rate is greater than $14.00?

SELECT 
    [p].[FirstName],
    [p].[MiddleName],
    [p].[LastName],
    [p].[Suffix],
    [e].[JobTitle],
    [eph].[PayFrequency],
    [eph].[Rate]
FROM 
    [Person].[Person] [p]
    JOIN [HumanResources].[Employee] [e] ON [p].[BusinessEntityID] = [e].[BusinessEntityID]
    JOIN [HumanResources].[EmployeePayHistory] [eph] ON [eph].[BusinessEntityID] = [p].[BusinessEntityID]
WHERE 
    [eph].[PayFrequency] = 1
    AND [eph].[Rate] > 14.00
ORDER BY 
    [eph].[Rate];
GO

What is the first name, middle name, last name, suffix, job title, pay frequency, and rate of employees paid once per month and their rate is greater than equal to $14.00?

To filter the above query further, let's add another AND to the WHERE clause for the next SQL statement.

Example 2: What employees have a pay frequency of 1 AND the rate is greater than $14.00 AND were hired on or after January 1, 2010?

SELECT 
    [p].[FirstName],
    [p].[MiddleName],
    [p].[LastName],
    [p].[Suffix],
    [e].[JobTitle],
    [eph].[PayFrequency],
    [eph].[Rate],
    [e].[HireDate]
FROM 
    [Person].[Person] [p]
    JOIN [HumanResources].[Employee] [e] ON [p].[BusinessEntityID] = [e].[BusinessEntityID]
    JOIN [HumanResources].[EmployeePayHistory] [eph] ON [eph].[BusinessEntityID] = [p].[BusinessEntityID]
WHERE 
    [eph].[PayFrequency] = 1
    AND [eph].[Rate] > 14.00
    AND [e].[HireDate] >= '2010-01-01'
ORDER BY 
    [eph].[Rate];
GO

What is the first name, middle name, last name, suffix job title, pay frequency, rate, and hire date of employees paid once per month and their rate is greater than equal to $14.00 and were hired on or after January1, 2010?

In this next example, we are going to do an aggregate query (average) and use the HAVING clause instead of the WHERE clause to filter out the results.

Example 3: What is the average rate of employees per department having a pay frequency of 1 AND the average rate is greater than $9.00?

SELECT 
    [edh].[DepartmentID],       
    AVG([eph].[Rate]) AS [AverageRate]
FROM 
    [HumanResources].[EmployeePayHistory] [eph]
    JOIN [HumanResources].[EmployeeDepartmentHistory] [edh] ON [eph].[BusinessEntityID] = [edh].[BusinessEntityID]
GROUP BY 
    [edh].[DepartmentID],
    [eph].[PayFrequency],
    [edh].[DepartmentID]
HAVING
    [eph].[PayFrequency] = 1
    AND AVG([eph].[Rate]) > 9.00
ORDER BY 
    [edh].[DepartmentID];
GO

What is the average rate of employees in pay frequency 1 by department?

SQL OR Operator

The OR operator returns records if any conditions specified in the WHERE Clause are true.

We can use the same example and just change the AND to OR.

Example 1: What employees have a pay frequency of 1 OR the rate is greater than $14.00?

SELECT 
    [p].[FirstName],       
    [p].[MiddleName],
    [p].[LastName],
    [p].[Suffix],
    [e].[JobTitle],
    [eph].[PayFrequency],
    [eph].[Rate]
FROM 
    [Person].[Person] [p]
    JOIN [HumanResources].[Employee] [e] ON [p].[BusinessEntityID] = [e].[BusinessEntityID]
    JOIN [HumanResources].[EmployeePayHistory] [eph] ON [eph].[BusinessEntityID] = [p].[BusinessEntityID]
WHERE 
    [eph].[PayFrequency] = 1
    OR [eph].[Rate] > 14.00
ORDER BY 
    [eph].[Rate];
GO

What is the first name, middle name, last name, suffix job title, pay frequency, and rate of employees paid once per month OR their rate is greater than equal to $14.00?

We can also add additional OR statement as follows.

Example 2: What employees have a pay frequency of 1 OR their rate is greater than $14.00 OR the employee's title is "Marketing Specialist"?

SELECT 
    [p].[FirstName],
    [p].[MiddleName],
    [p].[LastName],
    [p].[Suffix],
    [e].[JobTitle],
    [eph].[PayFrequency],
    [eph].[Rate]
FROM 
    [Person].[Person] [p]
    JOIN [HumanResources].[Employee] [e] ON [p].[BusinessEntityID] = [e].[BusinessEntityID]
    JOIN [HumanResources].[EmployeePayHistory] [eph] ON [eph].[BusinessEntityID] = [p].[BusinessEntityID]
WHERE 
    [eph].[PayFrequency] = 1
    OR [eph].[Rate] > 14.00
    OR [e].[JobTitle] LIKE 'Marketing Specialist'
ORDER BY 
    [eph].[Rate];
GO

Example 3: What is the average vacation and average sick hours by salaried or not salaried having average vacation hours greater than 40 OR average sick hours greater than 40?

SELECT 
    [SalariedFlag],
    AVG([VacationHours]) AS [AverageVacationHours],
    AVG([SickLeaveHours]) AS [AverageSickLeaveHours]
FROM 
    [HumanResources].[Employee]
GROUP BY 
    [SalariedFlag]
HAVING 
    AVG([VacationHours]) > 40
    OR AVG([SickLeaveHours]) > 40;
GO

What is the average vacation and sick hours by salaried or not salaried where either vacation or sicks hours are greater than 40?

SQL NOT Operator

Syntax: NOT boolean_expression

The NOT operator returns records that do not match the expression in the WHERE clause.

Example 1: What are all the credit card types NOT like "SuperiorCard"?

SELECT 
    DISTINCT [CardType]
FROM 
    [Sales].[CreditCard]
WHERE 
    [CardType] NOT LIKE 'SuperiorCard'
ORDER BY 
    [CardType];
GO

What are all the credit card types except for SuperiorCard?

Example 2: What products do we carry that color is NOT like black AND NOT like silver?

SELECT 
    [Name],
    [ProductNumber],
    [Color]
FROM  
    [Production].[Product]
WHERE 
    [Color] NOT LIKE 'Black'
    AND [Color] NOT LIKE 'Silver'
ORDER BY 
    [Name];
GO

What products do we carry that are neither black nor silver?

Combining AND, OR, and NOT SQL Operators in One Query

First, we'll combine the AND and OR operators.

Example 1: What are the purchases where Order Quantity is greater than or equal to 600 OR the Unit Price is greater than 80 AND the Stocked Quantity is greater than 1250?

Notice how we use parenthesis to group the UnitPrice and StockQty to make sure both of these conditions are met.

SELECT 
    [PurchaseOrderID],
    [OrderQty],
    [ProductID],
    [UnitPrice],
    [StockedQty]
FROM 
    [Purchasing].[PurchaseOrderDetail]
WHERE 
    [OrderQty] >= 600
    OR (
           [UnitPrice] > 80
           AND [StockedQty] > 1250
       );
GO

What is the Purchase Order ID, Order Quantity, Product ID, Unit Price, and Stocked Quantity where the Order Quantity is greater than or equal to 600 and the Unit Price > 80 and Stocked Quantity is greater than 1250?

Let's see what happens when we change the location of the parentheses slightly.

Example 2: What are the purchases where the Order Quantity is greater than or equal to 600 OR the Unit Price is greater than 80 AND the Stocked Quantity is greater than 1250?”

SELECT 
    [PurchaseOrderID],       
    [OrderQty],
    [ProductID],
    [UnitPrice],
    [StockedQty]
FROM 
    [Purchasing].[PurchaseOrderDetail]
WHERE 
    (
        [OrderQty] >= 600
        OR [UnitPrice] > 80
    )
    AND [StockedQty] > 1250;
GO

What is the Purchase Order ID, Order Quantity, Product ID, Unit Price, and Stocked Quantity where the Order Quantity is greater than or equal to 600 or the Unit Price > 80 and Stocked Quantity is greater than 1250?

Example 3: Finally, modify the above query to exclude Product IDs in the range of 300 and 400 by adding another AND with a NOT BETWEEN.

SELECT 
    [PurchaseOrderID],       
    [OrderQty],
    [ProductID],
    [UnitPrice],
    [StockedQty]
FROM 
    [Purchasing].[PurchaseOrderDetail]
WHERE 
    (
        [OrderQty] >= 600
        OR [UnitPrice] > 80
    )
    AND [StockedQty] > 1250
    AND [ProductID] NOT BETWEEN 300 AND 400;
GO

Next Steps

Here are some more T-SQL AND, OR, and NOT Operator tips for more information:

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-03-14

Learn how to write SQL Queries with AND, OR, and NOT Logical Operators