Subqueries 22405 Based on Introduction to Oracle SQL

Subqueries 2/24/05 Based on Introduction to Oracle: SQL and PL/SQL

Problem • Solution to question – Requires a two step query – Can use only one SQL statement • Example – Who has a salary greater than Jones’ salary? 2/24/05 Based on Introduction to Oracle: SQL and PL/SQL 2

Use a Subquery Main Query Which employees have a salary greater than Jones’ salary? Subquery What is Jones’ salary? 2/24/05 Based on Introduction to Oracle: SQL and PL/SQL 3

SELECT FROM WHERE select_list table expr operator (SELECT select_list FROM table) • Subquery (bottom) executed first • Outer query (top) executed last • Addition SQL clauses – can be added – Inner & Outer queries 2/24/05 Based on Introduction to Oracle: SQL and PL/SQL 4

Types of Subqueries • Single-row subquery Main query Subquery CLERK • Multiple-row subquery Main query returns Subquery CLERK MANAGER • Multiple-column subquery Main query Subquery returns CLERK 7900 MANAGER 7698 2/24/05 Based on Introduction to Oracle: SQL and PL/SQL 5

Single-Row Subqueries • Return only one row to Main Query • Use single-row comparison operators =, >, >=, <, <=, and <> 2/24/05 Based on Introduction to Oracle: SQL and PL/SQL 6

Example • Find the employee names and jobs for employees whose salary is greater than employee number 7876 and who have the same job as employee number 7369. 2/24/05 Based on Introduction to Oracle: SQL and PL/SQL 7

Multiple-Row Subqueries • Return more than one row • Use multiple-row comparison operators – IN, equal to any member in the list – ANY – ALL 2/24/05 Based on Introduction to Oracle: SQL and PL/SQL 8

Example • Find the employee name, salary, and department number for the employee who has the minimum salary in each department. 2/24/05 Based on Introduction to Oracle: SQL and PL/SQL 9