Given the Women table:
+-----+-----------+-------+
|  Id | FirstName | Score |
+-----+-----------+-------+
|   1 | Angelina  |  500  |
|   2 | Paris     |    0  |
|   4 | Jennifer  | NULL  |
|   7 | Misha     | 3000  |
+-----+-----------+-------+
How many records will the following query return:

SELECT * FROM Women
WHERE Score >= ALL (SELECT Score
                    FROM Women
                    WHERE FirstName='Eva');
Explanation
Whenever subquery is unable to return records, ALL operator automatically evaluates to True.
Theory
  • Logical Operators - Part1

    OperatorDescription
    ALLThe ALL operator is used to compare a value to all values in another value set
    ANDThe AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause
    ANYThe ANY operator is used to compare a value to any applicable value in the list according to the condition
    BETWEENThe BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value
    EXISTSThe EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria
    Read more.
  • Logical Operators - Part2

    Operator Description
    INThe IN operator is used to compare a value to a list of literal values that have been specified
    LIKE The LIKE operator is used to compare a value to similar values using wildcard operators.
    NOT The NOT operator reverses the meaning of the logical operator with which it is used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.
    OR The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.
    IS NULL The NULL operator is used to compare a value with a NULL value.
    UNIQUE The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates)
    Read more.

Follow CodeGalaxy

Mobile Beta

Get it on Google Play
Send Feedback