Sub Queries
Using a Sub-query :
Main Query: Which employees have salaries greater than Abel’s salary?
Subquery: What is Abel’s salary?
Sub-query
syntax :
The subquery (inner query)
executes once before the main query.
The result of the subquery is
used by the main query (outer query).
Syntax: Select select_list from table where
expr operator (select select_list from table);
Example : Select ename from emp where sal > (select sal from emp where ename = 'jones');
Types of Subqueries
1. Non-Correlated Sub-query:
• In Non-Correlated Sub-query“Child query” is executed first then only “Parent query” is executed.
Types of
Non-Correlated Sub-query:
•
Single
row Sub-query.
•
Multiple
row Sub-query.
•
Multiple
column Sub-query.
•
Inline
view (or) Sub-queryare using in “from” clause.
2. Correlated Sub-query:
• In Correlated Sub-query “Parent query” is executed first then only “Child query” is executed.
• Exists
• Not_Exist
Single-Row Subqueries :
· • Return only one row.
· • Use single-row comparison operators.
Executing Single-Row Subqueries :
SELECT ENAME, JOB, SAL
FROM EMP
WHERE JOB = (SELECT JOB
FROM
EMP
WHERE EMPNO = 7369) AND SAL >
FROM
EMP
WHERE EMPNO = 7876);
Using Group Functions in a Subquery :
SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL =
(SELECT MIN(SAL)
FROM
EMP);
The HAVING Clause with Subqueries :
· The
Oracle server executes subqueries first.
· The
Oracle server returns results into the HAVING clause of the main query
Example: SELECT
DEPTNO, MIN(SAL)
FROM EMP
GROUP BY DEPTNO HAVING MIN(SAL) >
(SELECT MIN(SAL)FROM EMP
WHERE DEPTNO = 30);
Multiple-Row Subqueries :
· Return
more than one row.
· Use multiple-row comparison operators
Example:
SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE SAL< ANY
(SELECT SAL
FROM EMP
WHERE
JOB = 'MANAGER')
AND JOB <> 'MANAGER';
Multiple-Column Subqueries :
· If
you want compare two or more columns.
· You
must write a compound WHERE clause using logical operators Multiple-column
subqueries enable you to combine duplicate WHERE conditions into a single WHERE
clause.
Example :
SELECT ordid, prodid, qty
FROM item
WHERE
(prodid, qty) IN
(SELECT
prodid, qty
FROM item
WHERE ordid
= 365)
AND ordid = 365;
Using the EXISTS Operator :
The EXISTS operator tests for existence of rows in the results set of the sub-query. If a sub-query row value is found the condition is flagged TRUE and the search does not continue in the inner query, and if it is not found then the condition is flagged FALSE and the search continues in the inner query.
EXAMPLE of using EXIST operator :
Find employees who have at least one person reporting to them.
SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS ( SELECT ’X’
FROM employees
WHERE manager_id = outer.employee_id);
EXAMPLE of using NOT EXIST operator :
Find all departments that do not have any employees.
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT ’X’
FROM employees
WHERE department_id = d.department_id);
No comments:
Post a Comment