- A correlated subquery references one or more columns in the outer query.
- The subquery is known as a correlated subquery because the subquery is related to the outer query.
- The alias is the pet name of a table which is brought about by putting directly after the table name in the FROM clause. This is suitable when anybody wants to obtain information from two separate tables.
SELECT a.ord_num,a.ord_amount,a.cust_code,a.agent_codeFROM orders a
WHERE a.agent_code= (
SELECT b.agent_code FROM agents b WHERE b.agent_name='Alex'
);
Using EXISTS
Using EXISTS the following query display
the employee_id, manager_id, first_name and last_name of those employees who
manage other employees.
SELECT employee_id, manager_id, first_name, last_name FROM employees a
WHERE EXISTS(
SELECT employee_id FROM employees b WHERE b.manager_id = a.employee_id);
Using NOT EXISTS
NOT EXISTS is used when we need to check if rows do not exist in the results returned by a subquery.
Using NOT EXISTS the following query display the employee_id, manager_id, first_name and last_name of those employees who have no manager status. This query is opposite to the previous one.
SELECT employee_id, manager_id, first_name, last_name FROM employees a WHERE NOT EXISTS
(
SELECT employee_id FROM employees b WHERE b.manager_id = a.employee_id
);
correlated subquery references one or more columns in the outer query
SELECT empno, mgr,ename, sal FROM empsubquery outer
WHERE sal >
(
SELECT AVG(sal) FROM empsubquery inner
WHERE inner.empno = outer.mgr);
SELECT empno, ename FROM empsubquery outer
WHERE EXISTS (
SELECT empno FROM empsubquery inner WHERE inner.mgr = outer.empno);
SELECT empno, ename FROM empsubquery outer
WHERE NOT EXISTS (
SELECT 1 FROM empsubquery inner WHERE inner.mgr = outer.empno);
