A subquery is a query within a query. In Oracle, you can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.
A subquery in the WHERE clause of a SELECT statement is also called a nested subquery. You can nest up to 255 levels of subqueries in the WHERE clause.
A subquery in the FROM clause of a SELECT statement is also called an inline view. Oracle Database imposes no limit on the number of subquery levels in the FROM clause of the top-level query.
A subquery can contain another subquery.
If columns in a subquery have the same name as columns in the containing statement, then you must prefix any reference to the column of the table from the containing statement with the table name or alias. To make your statements easier to read, always qualify the columns in a subquery with the name or alias of the table, view, or materialized view.
1. When a query is > ALL, each row in the result set is greater than the highest value returned.
SQL> SELECT empno, sal
2 FROM Emp
3 WHERE sal > ALL (SELECT sal
4 FROM Emp
5 WHERE job ='SALESMAN');
EMPNO SAL
---------- ----------
7566 2975
7698 2850
7782 2450
7788 3000
7839 5000
7902 3000
14 rows selected.
SQL>
2. 'ALL' with a Multiple Row Subquery.
'ALL' with a Multiple Row Subquery: must place an =, <>, <, >, <=, or >= operator before ALL
SQL> SELECT id, last_name 2 FROM employee 3 WHERE salary > ALL 4 (SELECT salary 5 FROM employee where description='Tester'); ID LAST_NAME ---- ---------- 07 Larry
3. Subquery > 'ANY' each row in the result set is greater than the lowest value returned.
SQL> SELECT *
2 FROM Emp
3 WHERE sal > ANY (SELECT sal
4 FROM Emp
5 WHERE job ='SALESMAN');
EMPNO ENAME JOB MGR HIREDATE SAL
-------- ---------- --------- ---------- --------- ----------
7839 KING PRESIDENT 17-NOV-81 5000
7902 FORD ANALYST 7566 03-DEC-81 3000
7788 SCOTT ANALYST 7566 09-DEC-82 3000
7566 JONES MANAGER 7839 02-APR-81 2975
7698 BLAKE MANAGER 7839 01-MAY-81 2850
7782 CLARK MANAGER 7839 09-JUN-81 2450
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
7844 TURNER SALESMAN 7698 08-SEP-81 1500
7934 MILLER CLERK 7782 23-JAN-82 1300
9 rows selected.
4. 'ANY' with a Multiple Row Subquery.
'ANY' with a Multiple Row Subquery: must place an =, <>, <, >, <=, or >= operator before ANY
SQL> SELECT id, last_name 2 FROM employee 3 WHERE salary < ANY 4 (SELECT salary 5 FROM employee where city='New York'); ID LAST_NAME ---- ---------- 08 Cat 01 Martin 05 Black 04 Rice 06 Green 03 Smith 02 Mathews 7 rows selected.
5. Use sub query as a virtual table(Inline Views)
SQL> SELECT id "Emp #", First_name "Name", salary "Salary", ROWNUM rank 2 FROM 3 (SELECT id, first_name, salary FROM employee ORDER BY salary); Emp Name Salary RANK ---- ---------- ---------- ---------- 01 Jason 1234.56 1 03 James 2334.78 2 08 James 2334.78 3 05 Robert 2334.78 4 06 Linda 2334.78 5 07 David 2334.78 6 02 Alison 2334.78 7 04 Celia 2334.78 8 8 rows selected.
6. Compare with data from subquery and Using of Aggregate Function
SQL> SELECT eName 2 FROM Emp 3 WHERE sal > (SELECT min(e.sal) 4 FROM Emp e, dept d 5 WHERE d.deptno = e.deptno);
7. An example of a nested three-level subquery
SQL> SELECT eName 2 FROM Emp 3 WHERE sal > (SELECT min(e.sal) 4 FROM Emp e, dept d 5 WHERE d.deptno = e.deptno);
8. If an inner query returns a NULL, the outer query also returns NULL
SQL> SELECT eName 2 FROM Emp 3 WHERE sal > (SELECT sal 4 FROM Emp 5 WHERE empno = 0); no rows selected
9. Working with multi-column subqueries(a pairwise comparison)
SQL> SELECT empno, ename 2 FROM Emp 3 WHERE (sal, deptno) = (SELECT sal, deptno FROM Emp WHERE empno = 7934);
10. Using the EXISTS and NOT EXISTS operators
SQL> SELECT empno, eName 2 FROM Emp e 3 WHERE EXISTS (SELECT 1 4 FROM dept d 5 WHERE e.deptno = d.deptno);
11. Writing Single Row Subqueries
SQL> SELECT first_name, last_name 2 FROM employee 3 WHERE id = 4 (SELECT id 5 FROM employee 6 WHERE last_name = 'Cat');
12. Subqueries in a HAVING Clause: Uses a subquery in the HAVING clause of the outer query
SQL> SELECT city, AVG(salary) 2 FROM employee 3 GROUP BY city 4 HAVING AVG(salary) < 5 (SELECT MAX(AVG(salary)) 6 FROM employee 7 GROUP BY city);
13. Subqueries May Not Contain an ORDER BY Clause
SQL> SELECT id, first_name, salary 2 FROM employee 3 WHERE salary > 4 (SELECT AVG(salary) 5 FROM employee) 6 ORDER BY id DESC;
14. Sub query with table join
SQL> SELECT e.empno, ename, j.jobtitle 2 FROM employee e, 3 (SELECT empno, jobtitle 4 FROM job) j 5 WHERE e.empno = j.empno;
15. Subquery just returns a literal value: improve performance of your query
SQL> SELECT empno, ename 2 FROM emp outer 3 WHERE EXISTS 4 (SELECT 1 5 FROM emp inner 6 WHERE inner.mgr = outer.empno);
16. EXISTS typically offers better performance than IN with subqueries
When a list of values contains a null, NOT EXISTS returns true, but NOT IN returns false.
SQL> SELECT empno, ename 2 FROM emp outer 3 WHERE NOT EXISTS 4 (SELECT 1 5 FROM emp inner 6 WHERE inner.mgr = outer.empno);
17. Writing Nested Subqueries
SQL> SELECT city, AVG(salary) 2 FROM employee 3 GROUP BY city 4 HAVING AVG(salary) < 5 (SELECT MAX(AVG(salary)) FROM employee WHERE id IN 6 (SELECT id FROM employee) 7 GROUP BY city);
18. subqueries in the SELECT column list (New Way)
SQL> SELECT e.lastname, e.salary, 2 (SELECT avg(salary) FROM employee 3 WHERE dept_no = e.dept_no) avg_sal_dept 4 FROM employee e 5 order by 1 6 /
19. subqueries in the SELECT column list (Old way)
SQL> SELECT e.lastname, e.salary, d.avg_sal_dept 2 FROM employee e, (SELECT dept_no, avg(salary) as avg_sal_dept 3 FROM employee 4 GROUP BY dept_no) d 5 WHERE e.dept_no = d.dept_no 6 order by 1 7 /
No comments:
Post a Comment