SQL Server logical operators and example

In this SQL Server tutorial, you will get to know about the various logical operators in SQL Server. I will explain how to use the logical operators with the help of some examples.

Different logical operators return different outputs. Let us see what are the various logical operators and how to use them.

  • List of logical operators in SQL Server
  • How to use AND operator in SQL Server
  • AND operator in SQL Server if statement
  • AND operator in SQL Server stored procedure
  • How to use OR operator in SQL Server
  • If condition with OR operator in SQL Server
  • OR operator in SQL Server stored procedure
  • ALL operator in SQL Server
  • ANY operator in SQL Server
  • How to use IN operator in SQL Server
  • IN operator in SQL Server stored procedure
  • LIKE operator in SQL Server
  • LIKE operator in SQL Server example
  • SQL Server LIKE operator wildcard
  • LIKE operator in SQL Server case sensitive
  • LIKE operator in SQL server with variable
  • LIKE operator in SQL Server stored procedure
  • LIKE operator in SQL Server for multiple values
  • BETWEEN operator in SQL Server
  • BETWEEN operator in SQL Server example
  • How to use BETWEEN operator for DateTime in SQL Server
  • EXISTS operator in SQL Server
  • NOT operator in SQL Server
  • SOME operator in SQL Server
  • Difference between ANY and SOME operator in SQL Server

List of logical operators in SQL Server

In SQL Server, the following are the logical operators that are supported:

OperatorReturn value
ANDTRUE, if both the boolean expressions are TRUE.
ORTRUE, if any of the two expressions is TRUE
ALLTRUE, if all comparisons are TRUE
ANYTRUE, if any of the comparisons are TRUE
INTRUE, if the operand is equal to any of the given list of expressions
LIKETRUE, if the operand value matches with the specified pattern
BETWEENTRUE, if the operand value lies between the specified range of values
EXISTSTRUE, if the specified subquery returns some rows
NOTReverses the result of any other logical operator
SOMETRUE, if some of the comparisons are TRUE
Logical operators in SQL Server

Read Arithmetic operators in SQL Server

How to use AND operator in SQL Server

The AND operator in SQL Server compares two boolean expressions and returns a TRUE value only if both of the boolean expressions are TRUE. Let us see the output of the AND operator in various use cases.

Expression 1Expression 2Output
FALSEFALSEFALSE
FALSETRUEFALSE
TRUEFALSEFALSE
TRUETRUETRUE
The output of the AND operator in various use cases

Now let us see how to use this operator in a SELECT statement. Look at the below SQL query:

SELECT TOP (1000) [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvince]
      ,[CountryRegion]
      ,[PostalCode]
  FROM [BackupDatabase].[SalesLT].[Address] WHERE CountryRegion ='United States' AND StateProvince = 'Washington'
How to use and operator in SQL Server
Using the AND operator in a SQL Server table

You can see only those records were returned where both the provided conditions were TRUE.

  • Condition 1: CountryRegion = “United States” i.e. TRUE
  • Condition 2: StateProvince = “Washington” i.e. TRUE

Thus, you might have learned how the AND operator works while working with SQL Server tables.

Read Comparison Operators in SQL Server

AND operator in SQL Server if statement

Let us see now how you can use the AND operator in an if statement.

DECLARE
	@num1 int = 12,
	@num2 int = 13,
	@num3 int = 15
IF @num1 = 12 AND @num2 = 13
	PRINT('The result is TRUE')
ELSE
	PRINT('The result is False')

IF @num1 = 12 AND @num3 = 0
	PRINT('The result is TRUE')
ELSE
	PRINT('The result is False')
AND operator in SQL Server if statement
The AND operator in an If statement

In the above T-SQL code, I have used two if blocks.

In the first if-block, both the boolean expressions are TRUE and hence, the final result will be TRUE.

In the second if-block, only one boolean expression is TRUE and the other is FALSE and hence, the final result is False.

Thus, you might have learned how you can use the AND operator with an if statement.

Read Create a table from view in SQL Server

AND operator in SQL Server stored procedure

In this section, I will explain an example where I will use the AND operator inside a SQL Server stored procedure.

USE BackupDatabase
GO
CREATE OR ALTER PROCEDURE dbo.SelectData @Color nvarchar(15), @Size nvarchar(5)
AS
BEGIN
	SELECT [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[Color]
      ,[StandardCost]
      ,[ListPrice]
      ,[Size]
      ,[Weight]
  FROM [BackupDatabase].[SalesLT].[Product] WHERE Color = @Color AND Size = @Size
END

This stored procedure will return a result set of products on the basis of the input parameters provided. Only those records will be returned where both the specified conditions will return a TRUE value i.e. Size and Color.

Now let us execute this stored procedure and provide the size and color values.

USE BackupDatabase
GO
EXEC dbo.SelectData 'Red', '60'
AND operator in SQL Server stored procedure
Records returned that satisfied both the given conditions

You can see in the output that the records were returned that specified both the conditions i.e Color = ‘Red’ and Size = ’60’.

Read SQL Server view order by

How to use OR operator in SQL Server

The OR operator in SQL Server returns a TRUE value if any of the two boolean expressions specified is TRUE. Below is the outcome of the OR operator in different scenarios:

Expression 1Expression 2Output
FALSEFALSEFALSE
FALSETRUETRUE
TRUEFALSETRUE
TRUETRUETRUE
The output of the OR operator in various use cases

Now let us see how we can use the OR operator in different ways.

If condition with OR operator in SQL Server

In this example, I will use the OR operator in an if statement. Consider the below T-SQL code:

DECLARE
	@num1 int = 12,
	@num2 int = 13,
	@num3 int = 15
IF @num1 = 12 OR @num2 = 0
	PRINT('The result is TRUE')
ELSE
	PRINT('The result is FALSE')
IF @num1 = 0 OR @num3 = 0
	PRINT('The result is TRUE')
ELSE
	PRINT('The result is FALSE')
If condition with OR operator in SQL Server
The OR operator in an if statement

In the above code, there are two if blocks.

In the first if-block, only one of the expressions is TRUE, and hence, the final result is TRUE.

In the second if-block, both the expressions are FALSE, and hence, the final result returned is FALSE.

Hence, in this way, you can use the OR operator with an if statement in SQL Server.

Read How to view table in SQL Server

OR operator in SQL Server stored procedure

Let us create a stored procedure now where we will use the OR operator to fetch a result set from a SQL Server table.

USE BackupDatabase
GO
CREATE OR ALTER PROCEDURE dbo.GetData @Color nvarchar(15), @Size nvarchar(5)
AS
BEGIN
	SELECT [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[Color]
      ,[StandardCost]
      ,[ListPrice]
      ,[Size]
      ,[Weight]
  FROM [BackupDatabase].[SalesLT].[Product] WHERE Color = @Color OR Size = @Size
END

This stored procedure will return those records from the table that will satisfy either of the two conditions or both i.e Color and Size.

Let us execute this stored procedure now and pass the Color and Size values as the input parameter.

USE BackupDatabase
GO
EXEC dbo.GetData 'Red', 'M'
OR operator in SQL Server stored procedure
The OR operator used with WHERE condition

You can see in the output that all the records were returned that satisfied either of the two conditions or both i.e. Color = ‘Red’ or Size = ‘M’.

In this way, you can use the OR operator inside a stored procedure to execute queries with conditions.

Read How to see view definition in SQL Server

ALL operator in SQL Server

The ALL operator in SQL Server can be used to compare a scalar value to a set of values and return a TRUE value only if all the values of the set satisfy the given condition.

We use a subquery result as a set of values and use this set with the ALL operator. For example:

SELECT * FROM Table WHERE Column1 = ALL(SELECT Column1 FROM AnotherTable)

Note that the data type of the single column value must be the same as that of the set of column values.

Now let us see an example. For this example, I will be using a table named dbo.RankCompany which is shown in the below image:

All operator in SQL Server
Sample Table

Suppose I have written a query:


SELECT [Rank]
      ,[Company Name]
      ,[Country]
  FROM [BackupDatabase].[dbo].[RankCompany] WHERE Rank < ALL(SELECT Rank FROM dbo.RankCompany WHERE RANK >5)

This will return output as:

SQL Server logical operators and example
Output after using the ALL operator

Now let me explain, how the ALL operator worked.

The subquery that we specified after the ALL operator was:

SELECT Rank FROM dbo.RankCompany WHERE RANK >5

This query returned the values: 6, 7, 8, 9, 10.

Now, these values are compared in our main query with the Rank column on our table.

For example, the first value in the Rank column is 1 in the table. It is compared with every value of the subquery i.e. with 6, 7, 8, 9, 10.

  • 1 < 6 is TRUE.
  • 1 < 7 is TRUE.
  • 1 < 8 is TRUE.
  • 1 < 9 is TRUE.
  • 1 < 10 is TRUE.

As all the comparisons returned TRUE, therefore, this record was returned from the table i.e. WHERE Rank = 1. Similarly, other records were also returned where the conditions were satisfied.

When the rank value at the scalar place was 6 and is compared to the set of values, the boolean results were:

  • 6 < 6 is FALSE.
  • 6 < 7 is TRUE.
  • 6 < 8 is TRUE.
  • 6 < 9 is TRUE.
  • 6 < 10 is TRUE.

You can see that one of the comparisons returned FALSE and as a result, the ANY operator returned FALSE and the record was not returned in the final result.

This shows that the ALL operator returns TRUE only if all the comparisons are TRUE when the scalar value is compared with a set of values.

Thus, you might have learned about the ALL operator in SQL Server 2019 and how it works.

Read How to delete a view in SQL Server

ANY operator in SQL Server

The ANY operator in SQL Server also compares a scalar value to a set of values returned by a subquery. You can compare it with the ALL operator.

The only difference between the two is that the ALL operator returns TRUE only when all the comparisons return a TRUE value while the ANY operator returns a TRUE value if even any one of the comparisons returns a TRUE value. Let us see an example.

Consider the same table that I used above i.e. dbo.RankCompany. I will be using the same T-SQL code. But this time, I will use the ANY operator instead of the AND operator.

SELECT [Rank]
      ,[Company Name]
      ,[Country]
  FROM [BackupDatabase].[dbo].[RankCompany] WHERE Rank < ANY(SELECT Rank FROM dbo.RankCompany WHERE RANK > 5)
Any operator in SQL Server
Output when the ANY operator is used

Now let us see how the ANY operator worked in the above query. The subquery again returned the values 6, 7, 8, 9, 10.

SELECT Rank FROM dbo.RankCompany WHERE RANK >5

These values will be compared by the scalar value specified in the main query. For example.

The first value in the Rank column is 1. It is compared with every value of the subquery i.e. with 6, 7, 8, 9, 10.

  • 1 < 6 is TRUE.
  • 1 < 7 is TRUE.
  • 1 < 8 is TRUE.
  • 1 < 9 is TRUE.
  • 1 < 10 is TRUE.

Now let us move to the Rank column value 9.

  • 9 < 6 is FALSE.
  • 9 < 7 is FALSE.
  • 9 < 8 is FALSE.
  • 9 < 9 is FALSE.
  • 9 < 10 is TRUE.

You can see that there is only one TRUE value, still, the record with Rank value 9 is returned in the output. Now let us see why the Rank value 10 is not returned.

  • 10 < 6 is FALSE.
  • 10 < 7 is FALSE.
  • 10 < 8 is FALSE.
  • 10 < 9 is FALSE.
  • 10 < 10 is FALSE.

All the comparisons returned FALSE values and hence the ANY operator returned a FALSE value and did not return this record.

Thus, you might have learned how the ANY operator works and how it is different from the ALL operator in SQL Server 2019.

Read SQL Server bulk insert from CSV file

How to use IN operator in SQL Server

The IN operator in SQL Server can be used to match a value with a set of values. The IN operator returns a TRUE value if the specified value gets matched with any value in the list of values. For example, look at the T-SQL code below:

USE BackupDatabase
GO
--When the value is specified in the set of values
IF 45 IN(34, 56, 64, 45, 65)
	PRINT('Value is in the list')
ELSE
	PRINT('Value not found in the list')
	
--When the value is not specified in the set of values
IF 45 IN(34, 56, 64, 65)
	PRINT('Value is in the list')
ELSE
	PRINT('Value not found in the list')
How to use IN operator in SQL Server
How to use the IN operator in SQL Server

In the first if-else statement, the specified value was available in the list. As a result, the IN operator returned a TRUE value.

In the second if-else statement, the specified value was not available in the list. As a result, the IN operator returned a FALSE value.

Thus, you might have got the basic idea of the IN operator in SQL Server. Now let us see some more examples. You will see how to use the IN operator with a SELECT statement.

Read SQL Server function return table

IN operator in SQL Server stored procedure

In this section, I will create a stored procedure where I will use the IN operator to retrieve a result set from a table in SQL Server.

Consider the below SQL Server stored procedure:

USE BackupDatabase
GO
CREATE OR ALTER PROCEDURE dbo.UseInOperator @Country1 nvarchar(20), @Country2 nvarchar(20)= ''
AS
BEGIN
	SELECT * FROM dbo.RankCompany WHERE Country IN (@Country1, @Country2)
END

The above stored procedure takes a minimum of one input parameter or two if the second one is specified and returns the records associated with these input values.

I have used the IN operator and it will match the Country column values with the input parameters specified in the list.

Now let us execute this stored procedure and provide two countries’ names i.e USA and Taiwan.

USE BackupDatabase
GO
EXEC dbo.UseInOperator 'USA', 'Taiwan'
IN operator in SQL Server stored procedure
IN operator in SQL Server stored procedure

You can see that all the records of the table were returned having the Country column values as either of the two values i.e. USA or Taiwan.

You can also execute this stored procedure with only one value. You will get the results accordingly. This is because I have used optional parameters in the stored procedure.

Similarly, you can create more optional parameters and use the IN operator to retrieve these kinds of results from a table.

Thus, you might have learned how to use the IN operator in SQL Server to compare a single value to a set of values and return a result in SQL Server.

Read How to test stored procedure in SQL Server

LIKE operator in SQL Server

The LIKE operator in SQL Server can be used to match a pattern with the values. We specify a pattern with the LIKE operator and this pattern is compared with the column values.

If you want to compare a string with another, you can do it using the equal to operator. But in a situation when you just want a string pattern to match with various string values, you can use the LIKE operator. Let us understand this with the help of an example.

LIKE operator in SQL Server example

In this section, I will explain an example of the LIKE operator. Consider the SQL query below:

SELECT TOP (1000) [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[Color]
      ,[StandardCost]
      ,[ListPrice]
      ,[Size]
      ,[Weight]
  FROM [BackupDatabase].[SalesLT].[Product] WHERE Name LIKE 'R%'

In the above SQL query, I have used the LIKE operator to find the records having a value starting with the character R in the Name column.

The % sign is a wildcard character that is used to specify a pattern. This sign represents a string of zero or multiple characters.

LIKE operator in SQL Server example
LIKE operator in SQL Server

There are more wildcard characters that you can use to specify a pattern according to your requirements. You will see them in the upcoming section.

Read SQL Server scheduled stored procedure

SQL Server LIKE operator wildcard

While using the LIKE operator, you can specify different types of patterns. There are some wildcard characters that you can use to specify various kinds of patterns. The below is the list of some wildcard characters and their usage.

CharacterUsageExample
%Specifies zero or more characters.‘abc%’
_Specifies a single character‘a_b’
[]A single character from one of the characters specified in the list.[ABCD]
[^]A single character from one of the characters in the list which you do not want to specify in the pattern[^ABCD]
[-]A single character among a range of characters [A-D]
Wildcard characters in SQL Server

Now let us see some examples related to these wildcard characters.

ExpressionDescription
%AAny string ending at the character ‘A’ e.g. USA
A%Any string starting from the character ‘A’ e.g. Austin
%A%Any string that has the ‘A’ character in it e.g. DALLAS or even Dallas if not case sensitive
_a%Any string that has the character ‘a’ in its second position e.g. Car
a__eAny string of having four characters that start from the character ‘a’ and ends at a character ‘e’ e.g. ache
[ab]%Any string that starts from the character ‘a’ or ‘b’.
[a-c]%Any string that starts from the character ‘a’ or ‘b’ or ‘c’.
[^A]%Any string that starts from any letter except the character ‘A’.
Examples of wildcard characters

Similarly, you can use create your own patterns on the basis of these wildcard characters. Hence, you might have learned how to use the wildcard characters with the LIKE operator in a SQL Server query.

Read SQL Server stored procedure case statement

LIKE operator in SQL Server case sensitive

The case sensitivity of the characters while using the LIKE operator depends on the collation that you are using. By default, the collation is case insensitive. You can check your default collation using the below SQL query:

SELECT CONVERT (varchar(256), SERVERPROPERTY('collation'));

By default, the collation is SQL_Latin1_General_CP1_CI_AS i.e. case insensitive. To know more about the various collations, you can read the Microsoft official documentation.

If the collation is case insensitive, the LIKE operator will return the same results for both the uppercase as well as lowercase letters.

If you want to make case-sensitive comparisons, you need to set the collation of the table or the column to be case-sensitive.

Although it is possible to change the collation for just string comparison using a query, it is not a recommended method as it is very costly in terms of performance.

Read Alter Stored Procedure in SQL Server

LIKE operator in SQL server with variable

In this section, you will see an example, where I have stored the matching pattern in a variable and I will use this variable in a SELECT query for retrieving a result set. Have a look at the SQL code below.

USE BackupDatabase
GO
DECLARE
	@var nvarchar(20)
	SET @var = 'BK%'
SELECT TOP (1000) [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[Color]
      ,[StandardCost]
      ,[ListPrice]
      ,[Size]
      ,[Weight]
  FROM [BackupDatabase].[SalesLT].[Product] WHERE ProductNumber LIKE @var

I have created a variable named @var and I have stored the pattern in it as a string. Later I used it in the SELECT statement. Let us see the output now.

LIKE operator in SQL server with variable
Using the LIKE operator with a variable

You can see that we got the expected output i.e. only those records are returned having the values matching with the pattern in the specified column.

This approach is very useful when you want to implement the pattern matching technique in stored procedures. You can see a similar example in the next section.

Read Rename stored procedure in SQL Server

LIKE operator in SQL Server stored procedure

In this section, I will create a stored procedure where I will use the LIKE operator to match a pattern and return records accordingly.

Look at the stored procedure below:

USE BackupDatabase
GO
CREATE OR ALTER PROCEDURE dbo.FindPhone @pattern nvarchar(8)
AS
BEGIN
	SELECT * FROM dbo.Persons WHERE Phone LIKE @pattern
END

The above stored procedure will take a pattern as an input. This pattern will be matched with the phone number values in the Phone column of the dbo.Persons table and return the records where the pattern will be matched.

For example, +1 is the country code of the United States of America, and 808 is the area code of Hawaii(a city in the United States of America).

If you want to find the records of the people having the phone number belonging to Hawaii, you can execute the above-stored procedure and pass a pattern as shown in the below code:

USE BackupDatabase
GO
EXEC dbo.FindPhone '+1 808%'
LIKE operator in SQL Server stored procedure
Using the LIKE operator in a SQL Server stored procedure

You can see in the results that all the records where the pattern matched with the values are returned. In this way, you can use the LIKE operator in a stored procedure using variables.

Read Could not find stored procedure in SQL Server

LIKE operator in SQL Server for multiple values

If you want to compare multiple patterns for comparisons, you need to use the AND or the OR operator for combining the boolean expressions.

For example, I can specify two patterns and use the OR operator between them. If any of the patterns are matched, the boolean result will be TRUE and the record will be returned in the result set. Let us see an example. Consider the SQL code below:

SELECT TOP (1000) [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[Color]
      ,[StandardCost]
      ,[ListPrice]
      ,[Size]
      ,[Weight]
  FROM [BackupDatabase].[SalesLT].[Product] WHERE ProductNumber LIKE 'FR%' OR ProductNumber LIKE 'SO%'

In the above SQL query, you can see that I have used the LIKE operator two times i.e. two boolean results. Also, I have used the OR operator to combine these two boolean values into one boolean result.

LIKE operator in SQL Server for multiple values
LIKE operator in SQL Server for multiple values

You can see in the output that all the records are returned where either of the patterns matched with the ProductNumber column. In this way, you can also use other operators for specifying multiple patterns for comparison.

Read SQL Server find text in stored procedure

BETWEEN operator in SQL Server

The BETWEEN operator in SQL Server is used to compare a scalar value to a range of values. Using the ANY or the SOME operator, you have to specify a set of values to be compared. But using the BETWEEN operator, you can specify a range.

The syntax of the BETWEEN operator is:

testing_expression BETWEEN start_value AND end_value

The range provided is inclusive i.e. the start_value and the end_value of the range are also included for comparison.

For example, look at the code snippet below:

USE BackupDatabase
GO
--First Expression
IF 23 BETWEEN 20 AND 25
	PRINT('TRUE')
ELSE
	PRINT('FALSE')

--Second Expression
IF 27 BETWEEN 20 AND 25
	PRINT('TRUE')
ELSE
	PRINT('FALSE')
BETWEEN operator in SQL Server
BETWEEN operator in SQL Server

In the first expression, we know that 23 lies between 20 and 25. As a result, a TRUE value is returned.

In the second expression, we know that 27 does not lie between 20 and 25. As a result, a FALSE value is returned.

Thus, you might have learned how you can use the BETWEEN operator in SQL Server.

Read Error: 40 – could not open a connection to sql server

BETWEEN operator in SQL Server example

Let us see one more example of the BETWEEN operator in SQL Server. This time I will use the BETWEEN operator in a SELECT statement. Look at the below T-SQL query.

SELECT [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[Color]
      ,[StandardCost]
      ,[ListPrice]
      ,[Size]
      ,[Weight]
      ,[ProductCategoryID]
  FROM [BackupDatabase].[SalesLT].[Product] WHERE ProductID BETWEEN 700 AND 710

I have used the ProductID column for comparison. The records having column values lying between the inclusive range of 700 and 710 will be returned in the result set.

BETWEEN operator in SQL Server example
BETWEEN operator in a SELECT statement

In this way, you can use the BETWEEN operator with a SELECT statement in SQL Server. Similarly, you can also use this operator with other DML statements also.

Read Full-text search in SQL Server

How to use BETWEEN operator for DateTime in SQL Server

You can also use the BETWEEN operator for comparing DateTime values. You can retrieve a result set from a table by specifying the range of DateTime values.

For this, you just have to specify the start date and the end date and the records within the date range will be returned. Also, take care of the format of the DateTime values. An incorrect format may not return you the desired results.

For example, look at the below SELECT query:

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (1000) [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[Color]
      ,[ListPrice]
      ,[SellStartDate]
      ,[SellEndDate]
      ,[DiscontinuedDate]
  FROM [BackupDatabase].[SalesLT].[Product] WHERE SellStartDate BETWEEN '2002-06-01 00:00:00.000' AND '2005-07-01 00:00:00.000'

In the above SELECT query, I have specified a range of DateTime data type values. All the records will be returned having the SellStartDate after the date “2002-06-01 00:00:00.000” and before the date “2005-07-01 00:00:00.000“.

How to use BETWEEN operator for DateTime in SQL Server
How to use BETWEEN operator for DateTime in SQL Server

You can see that all the values in the StartDate column are within the specified time period range. In this way, you can use the BETWEEN operator with the DateTime data type in SQL Server.

Read SQL Server stored procedure if exists update else insert

EXISTS operator in SQL Server

In this section, you will learn about the EXISTS operator in SQL Server. The EXISTS operator can be used to check the existence of a record in the result set returned by a subquery. For example:

USE BackupDatabase
GO
IF EXISTS(SELECT * FROM dbo.RankCompany WHERE Rank = 10)
	PRINT('Record Exists')
ELSE
	PRINT('Record does not exist')

IF EXISTS(SELECT * FROM dbo.RankCompany WHERE Rank = 100)
	PRINT('Record Exists')
ELSE
	PRINT('Record does not exist')
Exists operator in SQL Server
Exists operator in SQL Server

In the first if-else statement, there exists a column value 10 in the Rank column. Hence, the EXISTS operator returned a TRUE value.

However, in the second if-else statement, there is no such record having a Rank 100. Hence, the EXISTS operator returned a FALSE value.

In this way, you can use the EXISTS operator to check the existence of a record in a table or view.

Read SQL Server check user permissions on table

NOT operator in SQL Server

The NOT operator in SQL Server negates the value of any boolean expression. For example, if the boolean output is TRUE and you are using NOT with it, the final result will become FALSE. Let us see an example,

You have seen how the EXISTS operator works. This time I will use the NOT operator with the EXISTS operator. This time the above code will be modified as:

USE BackupDatabase
GO
IF NOT EXISTS(SELECT * FROM dbo.RankCompany WHERE Rank = 10)
	PRINT('Record does not exist')
ELSE
	PRINT('Record Exists')

IF NOT EXISTS(SELECT * FROM dbo.RankCompany WHERE Rank = 100)
	PRINT('Record does not exist')
ELSE
	PRINT('Record Exists')
NOT operator in SQL Server
NOT operator in SQL Server

You can compare this code with the above code. You can notice that the NOT operator is returning an opposite boolean output.

Thus, you might have learned how the NOT operator works in SQL Server.

Read How to execute stored procedure in SQL Server

SOME operator in SQL Server

The SOME operator in SQL Server is also used to compare a scalar expression to a set of values. It behaves the same as the ANY operator i.e. it also returns a TRUE value if some of the set of comparisons return a TRUE value.

I will use the same example that I used above to explain the ANY operator. But this time, I will use the SOME operator instead of the ANY operator.

SELECT [Rank]
      ,[Company Name]
      ,[Country]
  FROM [BackupDatabase].[dbo].[RankCompany] WHERE Rank < SOME(SELECT Rank FROM dbo.RankCompany WHERE RANK > 5)
SOME operator in SQL Server
SOME operator in SQL Server

You can clearly see in the image that the output is the same as it was in the case of the ANY operator.

Read How to check if SQL Server is running

Difference between ANY and SOME operator in SQL Server

There is no difference between the ANY and the SOME operator.

Both the operators are equivalent and work in the same way i.e. compare a scalar expression with a set of values returned by a subquery and returns a TRUE value if any or some of the set of comparisons results in a TRUE value.

You can verify this on the Microsoft official documentation.

Related SQL Server tutorials:

Thus, you might have learned about the various logical operators in SQL Server.

  • List of logical operators in SQL Server
  • How to use AND operator in SQL Server
  • AND operator in SQL Server if statement
  • AND operator in SQL Server stored procedure
  • How to use OR operator in SQL Server
  • If condition with OR operator in SQL Server
  • OR operator in SQL Server stored procedure
  • ALL operator in SQL Server
  • ANY operator in SQL Server
  • How to use IN operator in SQL Server
  • IN operator in SQL Server stored procedure
  • LIKE operator in SQL Server
  • LIKE operator in SQL Server example
  • SQL Server LIKE operator wildcard
  • LIKE operator in SQL Server case sensitive
  • LIKE operator in SQL server with variable
  • LIKE operator in SQL Server stored procedure
  • LIKE operator in SQL Server for multiple values
  • BETWEEN operator in SQL Server
  • BETWEEN operator in SQL Server example
  • How to use BETWEEN operator for DateTime in SQL Server
  • EXISTS operator in SQL Server
  • NOT operator in SQL Server
  • SOME operator in SQL Server
  • Difference between ANY and SOME operator in SQL Server