Introduction to Oracle Database subqueries

We gradually have a better understanding and knowledge of Oracle. Over time, we will explain the knowledge of SQL subqueries in this course, I hope the content in the course will help you!

1. Subquery: When a query result is a condition of another query, it is called a subquery.
2. Notes for using subqueries:
3. Subqueries can be nested in multiple layers
4. The subquery must be enclosed in parentheses ().
5. Subquery Syntax:
6. SELECT select_list
7. FROM table
8. WHERE expr operator
9. (SELECT select_list
10. FROM table );
11. Subquery (internal query) is executed once before the primary query.
12. The subquery results are used by the primary query (external query ).
13. Example: query the employee information whose salary is greater than JONES
14. The analysis process is as follows:
15. First, query the employee salary of JONES: Result2975
16. SQL> select sal from emp where ename ='Jones';
17. What we need to query is: the salary is higher2975The employee information is written as follows:
18. SQL> select * from emp where sal>2975;
19. // The results of the preceding subquery are as follows:
20. SQL> select * from emp where sal> (select sal from emp where ename ='Jones');
21. Note:
22. The subquery must be included in brackets.
23. Place the subquery on the right of the comparison condition.
25. Subqueries are classified into single-row subqueries and multi-row subqueries Based on the query results (nested query results,
26. Note:
27. Single-row operators correspond to single-row subqueries, and multi-row operators correspond to multiple-row subqueries.
28. Single Row Operator
29. >,>=, <, <=, <>, =
30. Example:
31. // Query information about employees in the same position numbered 7876 and whose salaries are greater than 7521
32. SQL> select * from emp where job = (select job from emp where empno =7876) And sal> (select sal from emp where empno =7521);
33. // Subquery contains group functions
34. SQL> select * from emp where sal> (select avg (nvl (sal,0) From emp );
35. // Subquery the department with the having clause to query the department with the minimum wage greater than the minimum wage of Department 20 and the minimum wage
36. SQL> select deptno, min (sal) from emp group by deptno having min (sal)> (select min (sal) from emp where deptno =20);
38. Note: The subquery can return empty rows without any query results.
40. Multi-row subquery
41. Multiple rows are returned.
42. Use the multi-line comparison operator.
43. Operators include:
44. Operator description
45. In is equal to any
46. Any subquery returns the same value and some
47. Comparison between All and All values returned by the subquery
48. Exists
50. // Query the information of any employee whose salary is less than the employee's CLERK and does not include the information of the employee whose job is CLERK.
51. SQL> select * from emp where sal <any (select sal from emp where job ='Cler') And job <>'Cler';
52. // Compare all with all values> all indicates that the value is greater than the maximum value of the query result.
53. SQL> select * from emp where sal> all (select sal from emp where job ='Cler') And job <>'Cler';
54. // The employee information with the same position number as 10 does not include the employee information.
55. SQL> select * from emp where job in (select job from emp where deptno =10) And deptno <>10;
57. Only thinking?
59. SQL> select * from emp where exists (select * from dept );
61. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
62. ----------------------------------------------------------------
63.  7369SMITH CLERK7902 1980-12-17800.0020
64.  7499ALLEN SALESMAN7698 1981-2-201600.00300.0030
65.  7521WARD SALESMAN7698 1981-2-221250.00500.0030
66. .....
68. SQL> select * from emp where exists (select * from dept where deptno =80);
70. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
71. ----------------------------------------------------------------
74. The above content is copyrighted by redarmy_chen. If you need to reprint it, please attach the source. If you have any questions, please send an email to redarmy_chen.@ Qq. Com