Tuple condition

Recall: the basic SELECT syntax:

select attribute-list from relation-list where condition

The condition in the where clause is known as:

tuple condition

Reason:

The where condition is applied to the following set of tuples:

select attribute-list from relation-list

All the possible tuple conditions in SQL

The tuple condition can be one of the following operation:

atomic-value relational-Oper atomic-value (we have seen this before)
The membership test operator (IN and NOT IN)
The any selection operator (ANY)
The all selection operator (ALL)
The non-empty set test operator (EXISTS)
The null test operator (IS NULL)
The wild card test operator (LIKE)
AND, OR and NOT expressions with any of the above tuple conditions

Tuple condition 1: "traditional" boolean condition

I will not explain what a boolean expression is
(You should have learned it in some intro course in programming)
All I want to say is:
The variables used in a boolean expression in SQL care:

Constants

Attribute names

Examples:

(1) where salary > 40000 // attr name and a constant (2) where dnumber = dno // 2 attr names and sex = 'F' // attr name and a (string) constant

Tuple condition 2: membership test (IN)

The membership test operator IN tests whether a value is contained in a set of values

Syntax:

atomic-value IN ( set of values )

meaning:

The expression evaluates to true if:
atomic-value is a member of the ( set of values )
Otherwise, the expression evaluates to false

Example:

SELECT fname, lname FROM employee WHERE ssn IN ('123456789', '333445555');

Tuple condition 2(A): NON-membership test (NOT IN)

The membership test operator NOT IN tests whether a value is not contained in a set

Syntax:

atomic-value NOT IN ( set of values )

meaning:

The expression evaluates to true if:
atomic-value is not a member of the ( set of values )
Otherwise, the expression evaluates to false

Example:

SELECT fname, lname FROM employee WHERE ssn NOT IN ('123456789', '333445555');

Attention: Pedagogical alert

Pedagogical alert:
I value understandability above complete correctness
What I am going to show you next is
not completely correct !!
However:
The explanation makes the concept(s) a whole lot easier to understand.

The ANY selector

Syntax of the ANY tuple condition:

atomic-value RelationalOperator any ( set of values )
RelationalOperator is one of: =, !=, <, <=, >, >= Example: salary >= any ( set of values )

meaning:

The expression evaluates to true if:
atomic-value RelationalOperator x
is true for at least one member of the ( set of values )
Otherwise, the expression evaluates to false

The ANY selector - example

Example: (illegal, but illustrative)

SELECT fname, lname FROM employee WHERE salary >= ANY ( 30000, 50000 )

Result of this SQL query:

Retrieves the name of employees whose salary is greater than or equal to some value in the set {30000, 50000}.
Since 30000 is the smallest value in the set, the condition salary >= ANY ( 30000, 50000 ) is equivalent to:
salary >= 30000
Therefore, this query retrieves the name of employees whose salary is greater than or equal to 30000.

The ANY selector - the correct syntax

Example: (illegal, but illustrative)

SELECT fname, lname FROM employee WHERE salary >= ANY ( 30000, 50000 ) // Explicit set of values

Note: the syntax above is illegal:

In SQL, you cannot use an explicit set of value
Instead, you must use a subquery that returns a set of values

Example:

SELECT fname, lname FROM employee WHERE salary >= ANY (SELECT salary // Subquery FROM employee)

The ALL selector

Syntax of the ANY tuple condition:

atomic-value RelationalOperator all ( set of values )
RelationalOperator is one of: =, !=, <, <=, >, >= Example: salary >= all ( set of values )

meaning:

The expression evaluates to true if:
atomic-value RelationalOperator x
is true for all member of the ( set of values )
Otherwise, the expression evaluates to false

The ALL selector - example

Example: (illegal, but illustrative)

SELECT fname, lname FROM employee WHERE salary >= ALL ( 30000, 50000 )

Result of this SQL query:

Retrieves the name of employees whose salary is greater than or equal to ALL values in the set {30000, 50000}.
Since 50000 is the largest value in the set, the condition salary >= ANY ( 30000, 50000 ) is equivalent to:
salary >= 50000
Therefore, this query retrieves the name of employees whose salary is greater than or equal to 50000.

The non-empty set test

Syntax of the non-empty set test:

exists ( set of values )
Example: (incorrect but illustrative) exists ( 'a', 'b' ) // returns true (= not empty) exists ( ) // returns false (= empty set)

meaning:

The expression evaluates to true if:
( set of values ) is not empty
Otherwise, the expression evaluates to false

Attention: Pedagogical alert CANCELED

The pedagogical alert is CANCELED
From now on, examples are syntactically correct

Checking for NULL values

Testing if some attribute value (in a tuple) contains the NULL value:
atomic-value IS NULL

Example: find all employee tuples that has a NULL value in the salary attribute:

SELECT * FROM employee WHERE salary IS NULL

Note:
To test if a attribute value is not equal to NULL, use:

atomic-value IS NOT NULL

Wild card string match (LIKE)

When comparing strings , you can use the LIKE operator to perform wildcard string comparison

Special wildcard characters:

Underscore ( _ ) matches exactly one character
( _ is equivalent to ? in the UNIX shell)
Percent (%) matches 0 or more characters
( % is equivalent to * in the UNIX shell)

Example: (finds employees with last name that starts with the letter S) SELECT fname, lname FROM employee WHERE lname LIKE 'S%'

Summary: all the possible tuple conditions in SQL

The tuple condition can be one of the following operation:

atomic-value relational-Oper atomic-value (we have seen this before)
The membership test operator (IN and NOT IN)
The any selection operator (ANY)
The all selection operator (ALL)
The non-empty set test operator (EXISTS)
The null test operator (IS NULL)
The wild card test operator (LIKE)
AND, OR and NOT expressions with any of the above tuple conditions