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

By:   |   Updated: 2023-08-07   |   Comments   |   Related: More > 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.

Logical Operator Returns True If Returns False If
ALL All subquery values meet the condition One or more subquery values are not met
AND All the conditions are met One or more conditions are not met
ANY Any of the conditions are met If no conditions are met
BETWEEN If a value is within a range including specified values If value is outside of specified range
EXISTS If any subquery values are returned If no values are returned from subquery
IN If equal to anything in expressions exists If no equal values are returned
LIKE If pattern is matched If no pattern is matched
NOT If condition is not met If condition is met
OR If any conditions are met If no conditions are met
SOME If any conditions are met If no conditions are met

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
ALL

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
AND

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
ANY

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
BETWEEN

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
EXISTS

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
IN

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
LIKE

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
NOT

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
OR

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
SOME
Next Steps

Check out these additional tips to learn about SQL operators:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Joe Gavin 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-08-07

Comments For This Article