SQL Server supports a number of different logical operators.  These operators can be used for testing Boolean conditions that return true, false and unknown in your T-SQL code.  Logical operators are useful for defining constraints to limit the rows be processed when selecting or updating data. This chapter will provide an overview of the logical operators supported by SQL Server and a few examples for some of those commonly used operators.

Logical Operators

Logical operators are one of a number types of operators supported by SQL Server.  Logical operators are used to test for TRUE, FALSE, or UNKNOWN conditions.   Figure 1 has an a list of the logical operators that can be found in the Microsoft documentation.

Figure 1: Logical Operators

These operators can be used by themselves or together with other operators in predicates to help build the appropriate business constrains when maintaining, managing, and reporting on data.   In order to understand how to use logical operators I will be providing a few examples that demonstrate how to use the BETWEEN, LIKE and IN logical operators to solve different processing requirements.   I will be using the AdventureWorks2017 database for my examples.  If you want to follow along and run the code in this chapter you can download a backup of the AdventureWorks2017 database from here.

BETWEEN operator

The BETWEEN operator allows you to test if an expression is within a specific range of values. The syntax for the BETWEEN operator, as shown in the Microsoft documentation, can be found in Figure 2.

Figure 2: Syntax of the BETWEEN operator

The BETWEEN operator will return a value of TRUE when expression being tested is greater than or equal to the begin value and less than or equal to the end value specified. If the expression being tested is not within the range then FALSE will be returned.  If any of the input to the BETWEEN operator is NULL then UNKNOWN is returned.

To show how the BETWEEN logical operator works, let’s assuming you want a report that shows the number of orders placed each date in January 2012.  To meet this reporting requirement  I will use the code in Listing 1.

Listing 1: Using BETWEEN operator

USE AdventureWorks2017;
GO
SELECT DISTINCT CONVERT(char(10),OrderDate,121) AS [OrderDateYYYY-MM-DD]
      ,COUNT(*) AS NumOfOrders
FROM Sales.SalesOrderHeader
WHERE OrderDate Between '2011-06-01'
                    and '2011-06-30'
GROUP BY OrderDate
ORDER BY [OrderDateYYYY-MM-DD];

The code in Listing 1 contains a SELECT statement that returns all order dates, in YYYY-MM-DD format and the number of orders placed, where the OrderDate values from the SalesOrderHeader table is between the value “2012-01-01” and “2012-12-31”.

When I run the code in Listing 1 I get the results in Figure 3.

Figure 3: Results when code in Listing 1 is run.

This example shows how the BETWEEN operator returns an inclusive range.  Meaning it returns all values in the range including the endpoint values.  If you want to specify an exclusive range, that doesn’t consider the end point values, then you will need to use the greater than (>) and/or less than (<) operators.

LIKE Operator

There may be a time when you want to find all the records in a table where a column contains a specific string somewhere within the column value.  Suppose you want to find all the last names that end in “sen”.  If you have this kind of selection criteria then the LIKE operator can be used to find those records.  The syntax for the LIKE operator as shown in the Microsoft documentation can be found in Figure 4.

Figure 4: Syntax for the LIKE operator

As you can see from the syntax in Figure 3, to use the LIKE operator you need to identify a pattern to search for.  That pattern can be the exact string you want to find, or a pattern can use wild cards if you want to search for different, but many similar strings.

To show how the LIKE operator works I will be search the LastName column of the Person.Person table looking for any LastName that starts with a “C” or an “H”, and ends in “sen”.  I will be using the code in Listing 2 to find the records that meet this search criteria.

Listing 2: Searching Last Names that match pattern

USE AdventureWorks2017
GO
SELECT FirstName, MiddleName, LastName
FROM  Person.Person
WHERE LastName LIKE '[C,H]%sen';

In Listing 2, I used a pattern value of '[C,H]%sen' to find those last names.  The “[C,H]” portion of the pattern, uses the single character wildcard specification to find those LastName’s that start with “C” or “H”.  To find just those LastName that ends in “sen” I use the wildcard specification of “%sen”.  This specification uses the “%” sign to match any string of characters, whereas the “sen” portion is used to identify the last name must end in “sen”.  When I run the code in Listing 2 I get the output found in Figure 5.

Figure 5: Records returned after running code in Listing 2

The power of wildcarding within the LIKE operator makes it easy to search for any pattern.

IN Operator

There may be a time when you need find records where a give column contains one or more different value.  In this situation the IN operator will be helpful.  The syntax, as found in the Microsoft documentation, for the IN operator can be found in Figure 6.

Figure 6: IN operator syntax

As you can see the IN operator accepts one or more subquery or an expression to identify the values you want to find or not find.  In order to show you how this works, I will first show a simple example that uses the IN operator to look for three different possible values.  The code for this example can be found in Listing 3.

Listing 3: Simple IN operator example

USE AdventureWorks2017
GO
SELECT Name, ListPrice
FROM  Production.Product
WHERE Name IN ('Road Tire Tube', 'Touring Pedal', 'Minipump');

By reviewing the code in Listing 3 you can see I’m using the IN operator to search the  Production.Product.Name column for three specific values.  When I run this code I get the output in Figure 7.

Figure 7: Output when code in Listing 3 is executed

Sometimes you might not know the exact character strings you want search for, but you know you could write a query or series of queries to identify the search values to find with the IN operator.      The code in Listing 7 uses a SELECT statement to identify the values to search for using the IN operator.

Listing 4: Using multiple queries in the IN operator

USE AdventureWorks2017
GO
SELECT Name, ListPrice
FROM  Production.Product
WHERE Name IN
(
 SELECT Name FROM Production.Product
  WHERE Name LIKE 'Road%Tube'
     OR Name LIKE 'Tour%Pe%'
     OR Name LIKE 'Mi%pump'
);

The code in Listing 4 uses that single SELECT statement to return a list of Name column values from the Production.Product table which will be searched for using the IN operator.  When the code in Listing 4 is run it returns the same results as Listing 3, which can be found in Figure 7.

One thing worth mentioning, is when you use a single subquery with the IN operator the subquery must reference only a single column name, but the query can return multiple values.  Whereas when you use multiple subqueries in the IN clause, as in Listing 5, then each subquery must return only single value.

Listing 5: Using Multiple Subqueries with IN operator

USE AdventureWorks2017
GO
SELECT Name, ListPrice
FROM  Production.Product
WHERE Name IN
(
 (SELECT Name FROM Production.Product
   WHERE Name LIKE 'Road%Tube' ),
 (SELECT Name FROM Production.Product
  WHERE Name LIKE 'Tour%Pe%'),
 (SELECT Name FROM Production.Product
   WHERE Name LIKE 'Mi%pump')
);

Each of the subqueries in Listing 5 returns just one value and this code returns the same results as Listing 3 and 4 (which can be found in Figure 7).   Note if I was to change the code for the second subquery to search for “Tour%P%”, it will return three rows.  If I was to do this the query would fail with the error found in Figure 8.

Figure 8: Error when subquery returns multiple rows.

Keep this requirement in mind if you use the IN clause with multiple subqueries.

Pitfalls of Using Logical Operators

You have already seen how the IN operator has some issues when multiple subqueries are used.  There are a few other pitfalls worth mentioning.  The first one is dealing with NULL values.

A NULL value in a column means the column doesn’t have a value.  Therefore when using logical operators to search for columns that contain NULL they can’t be found using the code in Listing 6.

Listing 6: Searching for Nulls

USE AdventureWorks2017
GO
SELECT Name, Color 
FROM Production.Product
WHERE Color IN ('White', 'Grey', NULL);

The code in Listing 6 will not return any products that have a NULL value for the Color column.  If you want to find the Production.Products that are “White”, “Grey”, or NULL then you have to convert those NULL values to a non-null value prior to comparing it using the IN logical operator. The code in Listing 7 will find all the products that have a NULL value for the Color attribute.

Listing 7: Finding NULL Colors

USE AdventureWorks2017
GO
SELECT Name, Color 
FROM Production.Product
WHERE COALESCE(Color,'') IN ('White', 'Grey', '');

In Listing 7 I using the COALESCE function to convert NULL values in the Color column to the empty string so it could be compared to values in the IN operator.

Does case matter when you use a logical operator?  The answer is “It Depends”.  If the column being proceeded with a logical operator contains upper and lower case and your database and/or column is not case sensitive then you will need to do some coding to find those case sensitive values.  To demonstrate how case-sensitivity may or may not affect the results of a query let me consider the code in Listing 8.

Listing 8: Code to show case-sensitivity issue

USE AdventureWorks2017
GO
DECLARE @Colors TABLE (Color varchar(15)) ;
INSERT INTO @Colors VALUES ('White'),
                           ('white'),
                          ('Grey'),
                          ('grey'); 
SELECT Color 
from @Colors
WHERE Color IN ('white','grey');

When I run the code in Listing 8, I get the results in Figure 9.

Figure 9: Results when executing code in Listing 8

The code in Listing 8 returned all the different colors regardless of the case-sensitivity of the Color column value.  Because the database AdventureWorks2017 is case-insensitive, the IN operator match both cases of the color white and grey.  If I only wanted to return the lower case white and grey colors I could use the COLLATE cause as I did in Listing 9.

Listing 9: Dealing with Case-Sensitive values

USE AdventureWorks2017
GO
DECLARE @Colors TABLE (Color varchar(15)) ;
INSERT INTO @Colors VALUES ('White'),
                           ('white'),
                           ('Grey'),
                           ('grey');
SELECT Color 
from @Colors
WHERE Color COLLATE Latin1_General_CS_AS IN ('white','grey');

In Listing 9 the COLLATE clause converts the Color column value to be case-sensitive.  By doing this conversion I can use the IN operator to perform case-sensitive searches.

Using Logical Operators

Using Logical Operators allows some additional ways to constrain and join SQL Server data using Boolean operations that equate to TRUE, FALSE or UNKNOWN.  These operators allow you not only specify characters string for your search criteria but also allow you to use wildcards and subqueries to identify those search values.  In this chapter I only covered a few logical operators.  I suggest you review the documentation to fully understand all the different logical operators available in SQL Server.