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

TRUE if all of a set of comparisons are TRUE.

TRUE if both Boolean expressions are TRUE.

TRUE if any one of a set of comparisons are TRUE.

TRUE if the operand is within a range.

TRUE if a subquery contains any rows.

TRUE if the operand is equal to one of a list of expressions.

TRUE if the operand matches a pattern.

Reverses the value of any other Boolean operator.

TRUE if either Boolean expression is TRUE.

TRUE if some of a set of comparisons are TRUE.

Table 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 