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

By: Joe Gavin | 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 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 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 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 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 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 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 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 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