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

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

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.