Teradata - SubQueries
A subquery returns records from one table based on the values from another table. It is a SELECT query within another query. The SELECT query called as inner query is executed first and the result is used by the outer query. Some of its salient features are −
A query can have multiple subqueries and subqueries may contain another subquery.
Subqueries doesn't return duplicate records.
If subquery returns only one value, you can use = operator to use it with the outer query. If it returns multiple values you can use IN or NOT IN.
Following is the generic syntax of subqueries.
SELECT col1, col2, col3,… FROM Outer Table WHERE col1 OPERATOR ( Inner SELECT Query);
Consider the following Salary table.
The following query identifies the employee number with highest salary. The inner SELECT performs the aggregation function to return the maximum NetPay value and the outer SELECT query uses this value to return the employee record with this value.
SELECT EmployeeNo, NetPay FROM Salary WHERE NetPay = (SELECT MAX(NetPay) FROM Salary);
When this query is executed, it produces the following output.
*** Query completed. One row found. 2 columns returned. *** Total elapsed time was 1 second. EmployeeNo NetPay ----------- ----------- 103 83000