SQL Logical Operators ALL, AND, ANY, BETWEEN, EXISTS, IN, LIKE, NOT, OR, SOME

By: Joe Gavin   |   Updated: 2023-08-07   |   Comments   |   Related: 1 | 2 | 3 | > TSQL

Problem

I need a better understanding of ways I can use SQL logical operators.

Solution

The purpose of logical operators is to test for the truth of some condition and return a Boolean value that can be true, false, or unknown. The following is a list of operators and what is returned under different conditions.

We'll step through each of the operators using a simple example. Each example is run in the AdventureWorks2019 database on a SQL Server 2022 server.

Use this tip, AdventureWorks Database Installation Steps, to show you two ways to install the database and if you want to copy and paste the SQL in any or all examples.

ALL

ALL returns true if all subquery values meet the condition. The subquery looks for all orders greater than 40, but the main query looks for all orders to have more than a quantity of 40. This query returns no records because not all the ProductIDs in the Sales.SalesOrderDetail table are greater than 40; therefore, a false is returned.

/* mssqltips.com */
SELECT [Name]
FROM [Production].[Product]
WHERE [ProductID]=ALL
(
    SELECT [ProductID] FROM [Sales].[SalesOrderDetail] WHERE [OrderQty] > 40
);
GO

AND

AND returns true if all specified conditions are met. It's the opposite of an OR. This query looks for all records with the name 'Pilar Ackerman'. All records with the last name Ackerman and the first name Pilar are returned.

 /* mssqltips.com */
SELECT [LastName]
     , [FirstName]
     , [MiddleName]
FROM [Person].[Person]
WHERE [LastName] = 'Ackerman'
      AND [FirstName] = 'Pilar'
ORDER BY [LastName];
GO

ANY

ANY returns true if any conditions are met, similar to an OR. This query is the same as the ALL example above, except the ALL has been changed to ANY. The subquery still looks for all orders greater than 40, and the main query is looking for any orders returned. So, here we see the name of any products where orders had a quantity greater than 40.

/* mssqltips.com */
SELECT [Name]
FROM [Production].[Product]
WHERE [ProductID]=ANY
(
    SELECT [ProductID] FROM [Sales].[SalesOrderDetail] WHERE [OrderQty] > 40
);
GO

BETWEEN

BETWEEN returns true if a value is within a specified range, including the range values. To see which employees were hired in the first quarter of 2013, we can search between 2013-01-01 and 2013-03-31. Syed Abbas was hired on 20-03-14. The same would be returned if the filter was 'WHERE [he].[HireDate] >= '2013-01-01' AND [he].[HireDate] <='2013-03-31''.

/* mssqltips.com */
SELECT [pp].[LastName]
     , [pp].[FirstName]
     , [pp].[MiddleName]
     , [he].[HireDate]
FROM [Person].[Person] [pp]
    JOIN [HumanResources].[Employee] [he]
        ON [pp].[BusinessEntityID] = [he].[BusinessEntityID]
WHERE [he].[HireDate]
BETWEEN '2013-01-01' AND '2013-03-31'ORDER BY [pp].[LastName];
GO

EXISTS

EXISTS returns true if the result of a subquery returns a value. It's commonly used to see if an object exists before dropping it. Here, we're checking for the existence of the table before querying it. The query of the HumanResources.Exployee table executes because it exists in sys.tables.

/* mssqltips.com */
IF EXISTS
(
    SELECT 1
    FROM sys.tables
    WHERE SCHEMA_NAME(schema_id) = 'HumanResources'
          AND name = 'Employee'
)
    SELECT [pp].[LastName]
         , [pp].[FirstName]
         , [pp].[MiddleName]
    FROM [Person].[Person] [pp]
    ORDER BY [pp].[LastName];
GO

IN

IN returns true if anything in an expression is true. It's the functional equivalent of searching for strings with the OR clause. This query returns any records with the group name 'Manufacturing' or 'Quality Assurance'.

/* mssqltips.com */
SELECT [GroupName]
     , [Name]
FROM [HumanResources].[Department]
WHERE [GroupName] IN ('Manufacturing', 'Quality Assurance')
ORDER BY [GroupName]
       , [Name];
GO

LIKE

LIKE can be used with or without wildcards and returns true if a string is matched. Here, we get all rows where the last name begins with 'ac' and ends with anything.

/* mssqltips.com */
SELECT [LastName]
     , [FirstName]
     , [MiddleName]
FROM [Person].[Person]
WHERE [LastName] LIKE 'ac%'
ORDER BY [LastName];
GO

NOT

NOT essentially negates what's in the WHERE clause. The above query returns records where the last name begins with 'ac'; putting a NOT in front of LIKE returns the records that do not begin with 'ac' and end with anything.

/* mssqltips.com */
SELECT [LastName]
     , [FirstName]
      ,[MiddleName]
FROM [Person].[Person]
WHERE [LastName] NOT LIKE 'ac%'
ORDER BY [LastName];
GO

OR

OR returns true if any criteria in the clause is met. This query returns records that either match the last name 'Zwilling' or the first name 'Gustavo'.

/* mssqltips.com */
SELECT [LastName]
     , [FirstName]
     , [MiddleName]
FROM [Person].[Person]
WHERE [LastName] = 'Zwilling'
      OR [FirstName] = 'Gustavo'
ORDER BY [LastName];
GO

SOME

SOME is like OR in that it returns true if any records match the criteria. Here are the product names for any order where the quantity is greater than 40 are returned, as we saw in the ANY example.

/* mssqltips.com */
SELECT [Name]
FROM [Production].[Product]
WHERE [ProductID]=SOME
(
    SELECT [ProductID] FROM [Sales].[SalesOrderDetail] WHERE [OrderQty] > 40
);
GO

Next Steps

Check out these additional tips to learn about SQL operators:

• Learn how to write SQL Queries with AND, OR, and NOT Logical Operators
• SQL NOT IN Operator
• T-SQL Bitwise Operators in SQL Server
• Understanding SQL Server Physical Joins
• SQL Comparison Operators to Refine Query Results
• SQL Server IN vs EXISTS
• SQL LIKE Statement
• SQL IN Operator
• SQL LIKE Statement for Various Text Patterns
• Build Conditional SQL Server Logic - SQL IF, BEGIN, END, ELSE, ELSEIF

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