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
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
|