Logical Operators (Transact-SQL)

Logical operators test for the truth of some condition. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE, FALSE, or UNKNOWN.

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.

Table

How Logical Operator works:

ALL

   

Compares a scalar value with a single-column set of values.

   

The following query returns all if all the StateCodes greater than 200. If atleast one statecode is less then 200 then it doesn,t return any records. Here States MP and UP have statecodes greater than 200, so condition fails and result is nothing.

   

SELECT * FROM  tbl_Population

WHERE 200 > ALL

(

      SELECT StateCode FROM tbl_Population

)

   

OUTPUT

Noyhing

   

   

AND

   

Performs a logical AND operation. The expression evaluates to TRUE if all conditions are TRUE.

   

SELECT * FROM tbl_Population

WHERE (StateCode > 100 AND StateCode < 200)

   

OUTPUT

---

   

ANY and SOME

   

Compares a scalar value with a single-column set of values.

   

Both SOME or ANY returns TRUE when the comparison specified is TRUE for ANY pair, otherwise, returns FALSE.

   

In the given table there is some states which statecodes are less than 200, so it will returns all the records.

   

ANY

   

SELECT * FROM  tbl_Population

WHERE 200 > ANY

(

      SELECT StateCode FROM tbl_Population

)

   

SOME

   

SELECT * FROM  tbl_Population

WHERE 200 > SOME

(

      SELECT StateCode FROM tbl_Population

)

   

OUTPUT

   

   

BETWEEN

   

Specifies a range to test.

   

SELECT * FROM tbl_Population

WHERE StateCode BETWEEN 100 AND 200

   

OUTPUT

EXISTS

   

Specifies a subquery to test for the existence of rows.

   

SELECT * FROM tbl_Population WHERE EXISTS

(

      SELECT * FROM tbl_Population

WHERE StateCode=409

)

   

It returns data when a specified record exist in the table which is given in sub query of where condition

   

OUTPUT

   

   

IN

   

Determines whether a given value matches any value in a subquery or a list.

   

SELECT * FROM tbl_Population

WHERE StateCode IN (1,101,102,300)

   

OUTPUT

LIKE

   

Determines whether a specific character string matches a specified pattern. A pattern can include regular characters and wildcard characters. During pattern matching, regular characters must exactly match the characters specified in the character string. However, wildcard characters can be matched with arbitrary fragments of the character string. Using wildcard characters makes the LIKE operator more flexible than using the = and != string comparison operators. If any one of the arguments are not of character string data type, the SQL Server 2005 Database Engine converts them to character string data type, if it is possible.

   

SELECT * FROM tbl_Population

WHERE StateName LIKE 'K%'

   

Returns all the records which has K as first letter in StateName

   

OUTPUT

NOT

   

To find rows that do not match a value, use the NOT operator.

   

SELECT * FROM tbl_Population

WHERE StateCode NOT IN (1,100,200,300)

   

OUTPUT

   

   

OR

Performs a logical OR operation. The expression evaluates to TRUE if atleast one condition is TRUE.

   

SELECT * FROM tbl_Population

WHERE StateName LIKE 'K%' OR StateCode < 105

   

OUTPUT