17.05 - Example: Simple Subquery Using the ALL Logical Predicate - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

The following SELECT statement retrieves the employees with the highest salary and the most years of experience:

     SELECT emp_no, name, job_title, salary, yrs_exp
     FROM employee
     WHERE (salary,yrs_exp) >= ALL (SELECT salary,yrs_exp
                                    FROM employee);
The query returns this many rows … WHEN this condition evaluates to TRUE …
0 employees with the highest salary do not also have the most years experience.
1 there is only one employee with the highest salary and years experience.
multiple there is more than one employee with the highest salary and years experience.

For the current data in the employee table, the result looks like this:

     EmpNo Name      JobTitle     Salary  YrsExp
     ----- --------- --------- --------- -------
     10018 Russell S President 65,000.00      25

The result shows that one employee has both the highest salary and the most years of experience.