---

The "harder-to-explain" tuple-conditions

---

 

 

 

• Pedagogical alert: I value understandability before complete correctness

  What I am going to show you in this webpage is not syntactically correct But, the "simplified but erroneous" syntax that I will be using, will make the concepts a whole lot easier to understand

  ---

  ---

  ---

  ---

   

   

   

   

  ---

  ---

  ---

  ---

• The ANY comparison operator
  • Syntax:

    atomic-value RelOp ANY ( set of values ) RelOp can be any relational operator: =, !=, <, <=, >, >= Example: 100 >= any ( 300, 50 ) // Evaluates to true because 100 >= 50 100 >= any ( 300, 500 ) // Evaluates to false (can't find any value) salary > any ( set of values ) salary <= any ( set of values )

    Meaning of the any-expression:

    The condition evaluates to true if: There is at least one value ∈ ( set of values ) that makes the condition true The condition evaluates to false otherwise

    ---

  • Example: (syntax is illegal in SQL, but it's illustrative)

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

    Result of this query:

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

  ---

  ---

  ---

  ---

• Pedagogical clarification
  • Why is this syntax illegal:

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

    Reason:

    You cannot use an explicit set of values in the ANY-clause The set of values in the ANY-clause: atomic-value RelOp ( set-of-values ) must be computed using a SELECT-query (And I don't want to explain how this is done right now; but delay it for later)

  ---

  ---

  ---

  ---

• The ALL comparison operator
  • Syntax:

    atomic-value RelationalOperator ALL ( set of values ) RelationalOperator is all relational operator: =, !=, <, <=, >, >= Example: 100 >= all ( 30, 50 ) // Evaluates to true (100 >= all values) 100 >= all ( 30, 500 ) // Evaluates to false (because 100 not >= 500) salary > all ( set of values ) salary <= all ( set of values )

    Meaning of the all-condition:

    The condition evaluates to true if: For every value ∈ ( set of values ), the condition evaluates to true       The condition evaluates to false otherwise

    ---

  • Example: (syntax is illegal in SQL, but it's illustrative)

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

    Result of this query:

    Retrieves the name of employees whose salary is ≥ (greater than or equal) to all (= every) values in the set {30000, 50000}. Note: Since 50000 is the largest value in (30000, 50000), the all-condition: salary >= ALL ( 30000, 50000 ) is equivalent to: salary >= 50000

    ---

    ---

  • Note:

    The ALL-clause cannot use an explicit set of values            (Just like the ANY-clause)

  ---

  ---

  ---

  ---

• There is no need to use = and != in an ANY or ALL clause
  • Comment:

    There is no need to use: Reason: ------------- = ANY ≡ IN != ALL ≡ NOT IN != ANY useless = ALL useless

    The reasons are explained next....

  ---

  ---

  ---

  ---

• The "= ANY"-clause is the same as "IN"-clause
  • Example of the = ANY clause:

    SELECT fname, lname FROM employee WHERE ssn = ANY ('111-11-1111', '222-22-2222')

    meaning:

    The ssn of employee is equal to any (one) value in the set:            ('111-11-1111', '222-22-2222')

    The condition is equivalent to:

    SELECT fname, lname FROM employee WHERE ssn IN ('111-11-1111', '222-22-2222')

  ---

  ---

  ---

  ---

• The "!= ALL"-clause is the same as the "NOT IN"-clause
  • Example of the != ALL clause:

    SELECT fname, lname FROM employee WHERE ssn != ALL ('111-11-1111', '222-22-2222')

    meaning:

    The ssn of employee is not equal to every value in the set ('111-11-1111', '222-22-2222')

    The condition is equivalent to:

    SELECT fname, lname FROM employee WHERE ssn NOT IN ('111-11-1111', '222-22-2222')

  ---

  ---

  ---

  ---

• Warning: do not use: "= ALL" and "!= ANY" clauses !!!
  • Facts:

    = ALL will (almost) always evaluate to false != ANY will (almost) always evaluate to true

    ---

    ---

  • Examples:

    x = ALL ( 1, 2 ) is always FALSE !!! // Try: x = 0 --> 0 != 1, so false // Try: x = 1 --> 1 != 2, so false // Try: x = 2 --> 2 != 1, so false // Try: x = 3 --> 3 != 1, so false // And so on... x != ANY ( 1, 2 ) is always TRUE !!! // Try: x = 0 --> 0 != 1, so true // Try: x = 1 --> 1 != 2, so true // Try: x = 2 --> 2 != 1, so true // Try: x = 3 --> 3 != 1, so true // And so on...

    ---

  • Conclusion:

    You don't really need to use "= ANY", "= ALL", "!= ANY" and "!= ALL". Just stick with IN and NOT IN.

  ---

  ---

  ---

  ---

  ---

  ---

  ---

  ---

• The exists (non-empty set) test operator
  • Syntax:

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

    Meaning of the EXISTS-condition:

    The EXISTS-condition evaluates to true if The ( set of values ) is an not equal to the empty set         (I.e.: a value exists in the set) The EXISTS-condition evaluates to false otherwise (I.e.: the (set of values) is the empty set)