Associate subqueries and nested subqueries

Associate subquery and nested subquery SQL code www.2cto. comcreatetableEMP (EMPNONUMBER (4) notnull, ENAMEVARCHAR2 (10), JOBVARCHAR2 (9), MGRNUMBER (4), HIREDATEDATE, SALNUMBER (7, 2), DEPTNONUMBER (2, to query information of all employees lower than the average salary of the Department

SQL code www.2cto.com create table EMP (EMPNO NUMBER (4) not null, ENAME VARCHAR2 (10), JOB VARCHAR2 (9), MGR NUMBER (4 ), hiredate date, sal number (), deptno number (2); as shown in the table above, you need to query information about all employees lower than the average salary of your department.

Associate subqueries and nested subqueries

SQL code www.2cto.com

Create table EMP

(

Empno number (4) not null,

ENAME VARCHAR2 (10 ),

JOB VARCHAR2 (9 ),

Mgr number (4 ),

Hiredate date,

Sal number (7,2 ),

Deptno number (2)

);

As shown in the table above, you need to query information about all employees lower than the average salary of your department.

Nested subquery:

SQL code

Select * from emp a where a. sal <(select avg (sal) from emp B where B. deptno = a. deptno)

It can be seen that each record must be associated with a subquery (each record must first query its own deptno and then subquery), which is not efficient.

Associated subquery:

SQL code

Select a. * from emp a, (select deptno, avg (sal) sal from emp group by deptno) B

Where a. deptno = B. deptno

And a. sal <B. sal;