The WHERE clause
in the SELECT command contains
a condition
that is applied to
each tuple
in the cartesian product
(formed by the
FROM clause
of the SELECT command)
As such:
The WHERE clause of the
SQL command
is also known as a
tuple condition
The following is a list of possible (allowable) forms of
tuple conditions :
Boolean expressions
using constants and/or
attributes
Examples:
1. salary > 50000
2. dno = dumber
atomic-valueIN(SET of values)
Examples:
1. ssn IN ( '111111111', '123456789' )
2. lname IN ( 'Smith', 'Wesson' )
atomic-valueNOT IN(SET of values)
Examples:
1. ssn NOT IN ( '111111111', '123456789' )
2. lname NOT IN ( 'Smith', 'Wesson' )
atomic-valueReationalOperator ANY(SET of values)
Example:
salary > ANY ( 30000, 50000 )
Note:
You cannot use an explicit set
You must use a set obtained by a sub-query
atomic-valueReationalOperator ALL(SET of values)
Example:
salary > ALL ( 30000, 50000 )
Note:
You cannot use an explicit set
You must use a set obtained by a sub-query
EXISTS(SET of values)
Examples:
exists ( 30000, 50000 ) // returns true
exists ( ) // returns false
Note:
You cannot use an explicit set
You must use a set obtained by a sub-query
atomic-valueIS NULL
Examples:
1. lname IS NULL // Check for null value
2. salary IS NULL //
atomic-valueLIKE
'wildcard-expression'
Examples:
1. lname like 'S%' // "S*"
2. lname like '___' // Any 3 letter word
Any combination of the
abovetuple conditions using
logical operators NOT/AND/OR:
NOTtuple-condition
Example:
NOT ( salary > 50000 )
tuple-condition AND tuple-condition
Example:
( dno = 5 ) AND ( salary > 50000 )
tuple-condition ORtuple-condition
Example:
( dno = 5 ) OR ( dno = 1 )
Next: hwo to use
these new tuple conditions ?
Next:
We will studyhow to use
the newtuple conditions
in SQL queries that
contain sub-queries