Social Icons

Subqueries

TeradataWiki-Teradata sub queries
Subqueries means when you write SELECT statement within the another SELECT statement. The Inner SELECT will be input for the outer SELECT.

Below are the different types of examples.
  • Find all the employees who are department managers.
SELECT last_name
FROM employee
WHERE employee_number  IN
(SELECT manager_employee_number

FROM department);

last_name
---------
Trader
Ryan
Runyon

Note:  Subqueries always produce a DISTINCT list of values

SELECT Subquery with AND
  • Who are the department managers whose salaries are less than 550000 and whose budget amounts are greater than 950000?
SELECT last_name
,first_name
FROM employee
WHERE salary_amount< 550000
AND (SELECT manager_employee_number
FROM department
WHERE budget_amount > 950000 );

last_name first_name
-----------   ------------
Ryan         Loretta

SELECT Subquery with Quantifier ANY 
  • Who are the employees who work in support departments?
SELECT last_name
,first_name
,department_number
FROM employee
WHERE department_number = ANY
(SELECT department_number 
FROM department
WHERE department_name LIKE%Support%’);

last_name     first_name department_number
-----------       -----------      ----------------------
Daly              James           402
Trader           James           401
Johnson         Darlene      401
Rogers          Frank           401
    :               :                :  

EXISTS with Subquery
  • Do any departments have no employees?
SELECT ‘YES’
WHERE EXISTS
(SELECT department_number FROM department
WHERE department_number NOT IN
(SELECT department_number
FROM employee));
  • Do any employees work in department 600?
SELECT ‘TRUE’
WHERE EXISTS
(SELECT *
FROM employee
WHERE department_number =600);

Rules for Subqueries

  1. Must be enclosed in parentheses
  2. Can be the object of an IN or NOT IN clause
  3. Can be the object of EXISTS or NOT EXISTS clause
  4. Support quantifiers ALL, ANY, SOME
  5. Support LIKE or NOT LIKE used with a quantifier
  6. Can specify more than one column to match
  7. Generate a DISTINCT list of values
  8. Cannot use ORDER BY (within the subquery statement).
  9. Keep in mind that a maximum of 64 tables/views can be specified in an SQL statement.