Example: Simple Subquery Using the ALL Logical Predicate - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
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.