ALL Operator

The ALL operator returns True when all of the records in query result set pass the Boolean condition.

A bit more explanation.  The ALL statement gives a TRUE value when: 1) the subquery returns no records, or 2) all records meet the comparison.  ALL will return false if even one record fails the Boolean condition.  If any of the values are NULL, then the operation value will be NULL, not TRUE.

For example, we want to see which authors currently have no titles:

SELECT au_id
FROM authors
WHERE au_id <> ALL(SELECT au_id FROM titleauthor)

Syntax

SELECT …
WHERE expression comparison {ALL | ANY | SOME} ( subquery )

Explained

WHERE expression

Tests a single value, such as a column, against all other values in the subquery. All rows need to match the expression to return a Boolean TRUE value for the ALL operator to return a Boolean TRUE value.

comparison

Compares the expression to the subquery.  The comparison must be a standard comparison operator like =, <>, !=, >, >=, <, or <=.

Advice

ALL is unique in that it guarantees that all records in the result set evaluate to TRUE.

DBMS Variations

All the major commercial database platforms support ALL.

Oracle supports a minor variation in that you can supply a list of values instead of a subquery.  For example, we can find all employees who have a job_lvl value equal to 9 or 14:

SELECT * FROM employee
WHERE job_lvl = ALL(9, 14);

DB2 and SQL Server supports some additional comparison operators with ALL, including not greater than (!>) and not less than (!<).

Related Entries

BETWEEN

EXISTS

IN

Operators

SELECT

SOME

WHERE

Google Author

BETWEEN operator

BETWEEN performs a Boolean True/False test to determine whether or not a value falls within a particular range, including the values mentioned in the BETWEEN clause.  BETWEEN returns TRUE when in the range, FALSE when not in the range, and NULL if any range values are unknown. It can’t be used with data type like BLOB, CLOB, NCLOB, REF, and ARRAY.

Syntax

SELECT …
WHERE expression [NOT] BETWEEN bottom_of_range AND top_of_range

Explained

WHERE expression

Compares an expression, that is, a single value like a column value or a literal expression, to the values within top_of_range and bottom_of_range. The addition of the optional [NOT] keyword tells the statement to look outside the values of that particular range.

For example, if we want to find employees with a hire_date between 01-Jan-2010 and 31-Dec-2013:

SELECT last_name, first_name, emp_ID
FROM employees
WHERE hire_date BETWEEN '01/01/2010' AND '31/12/2013';

To search for values outside of the BETWEEN range, just use the NOT keyword.  For example, if you want to see employees hired outside of 2013:

SELECT last_name, first_name, emp_ID
FROM employees
WHERE hire_date NOT BETWEEN '01/01/2013' AND '31/12/2013';

Advice

Since the AND keyword is used WHERE and JOIN clauses to compound conditions, many SQL developers surround the BETWEEN clause with parenthesis for better isolation and readability:

SELECT last_name, first_name, emp_ID
FROM employees
WHERE hire_date NOT BETWEEN ('01/01/2013' AND '31/12/2013')
  AND status = 'A';

 

Related Entries

ALL | ANY/SOME
EXISTS

IN
OPERATORS
SELECT
SOME
WHERE

Google Author

ANY / SOME operators

The ANY and SOME operators, which are synonyms, perform a Boolean test returning true if even one record in the result set matches the Boolean condition. ANY/SOME is semantically equivalent to IN and EXISTS, but much less widespread.  To ensure easy maintenance of your code, use IN or EXISTS rather than ANY/SOME wherever possible.

A bit more explanation,  ANY/SOME operators return TRUE when at least one record matches and return FALSE when none match or when there’s an empty result set.  If any of the values are NULL, then the operation value will be NULL, not TRUE.

ANY/SOME can be used for filtering checks similar to an IN or EXISTS subquery. In this example, we will get any employee records that also exist in the employees_archive table with a city of Anchorage.

SELECT *
FROM employees
WHERE job_lvl = ANY(SELECT job_lvl FROM employees_archive
                    WHERE city = ‘Anchorage’)

 

Syntax

SELECT …
WHERE expression comparison {ANY | SOME} ( subquery )

Explained

WHERE expression

Tests a single value, such as a column, against every value until a match is found for ANY and SOME.  One or more rows must match the expression, for ANY and SOME, to return a Boolean TRUE value.

comparison

Compares the expression to the subquery.  The comparison must be a standard comparison operator like =, <>, !=, >, >=, <, or <=.

Related Entries

BETWEEN

EXISTS

IN

Operators

SELECT

SOME

WHERE

Google Author