SQL Logical Operators

Logical Operators (Transact-SQL)

In T-SQL, we have following logical operators:

Operator	Meaning
ALL	TRUE if all of a set of comparisons are TRUE.
AND	TRUE if both Boolean expressions are TRUE.
ANY	TRUE if any one of a set of comparisons are TRUE.
BETWEEN	TRUE if the operand is within a range.
EXISTS	TRUE if a subquery contains any rows.
IN	TRUE if the operand is equal to one of a list of expressions.
LIKE	TRUE if the operand matches a pattern.
NOT	Reverses the value of any other Boolean operator.
OR	TRUE if either Boolean expression is TRUE.
SOME	TRUE if some of a set of comparisons are TRUE.

For example , we have two tables as following:

Address table

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger
4	Srivastava	Hemant	Cranbrook 104	Cockeysville
5	Kumar	Sunil	York 40	Towson

Age table

P_Id	Age
1	22
2	25
3	22
4	30
5	25

 

·       IN Operator

SELECT * FROM ADDRESS WHERE P_Id IN(2,4)

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes
4	Srivastava	Hemant	Cranbrook 104	Baltimore

SELECT * FROM ADDRESS WHERE P_Id IN(SELECT P_Id FROM AGE WHERE AGE >= 25)

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes
4	Srivastava	Hemant	Cranbrook 104	Baltimore
5	Kumar	Sunil	York 40	Towson

·       LIKE OPERATOR

SELECT * FROM ADDRESS WHERE LastName LIKE '%en'

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

·       NOT OPERATOR

Negates the value of the Boolean expression. NOT (Boolean Expression)

SELECT * FROM ADDRESS WHERE LastName NOT LIKE '%en'

Is same as

SELECT * FROM ADDRESS WHERE NOT (LastName  LIKE '%en')

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes
4	Srivastava	Hemant	Cranbrook 104	Baltimore
5	Kumar	Sunil	York 40	Towson