Correlated sub-queries


https://www.youtube.com/watch?v=SM9cDMxAeK4

Where inner query will be executed for each row of outer query ,


Difference between sub Query/Inner query and correlated nested sub-query 

Lest see by real example


EMPLOYEE table
EmployeeId
NAME
Salary
DepartmentId
1
Sandeep

2
2


2
3


1
4


6

Requirement 1:

FIND EMPLOYEE NAME, WHOSE SALARY IS GREATER THAN THE AVG SALARY OF ALL THE EMPLOYEE

Requirement 2:

FIND EMPLOYEE NAME, WHOSE SALARY IS GREATER THAN THE AVG SALARY OF HIS DEPARTMENT.


QUERY for Requirement-1

select NAME from EMPLOYEE where salary > (select avg(salary) from EMPLOYEE).

Here the inner query is executed only once, resulted in average salary from all the employee, and then outer query is executed once.




QUERY for Requirement -2


select ename from EMPLOYEE AS E  where salary > (select avg(salary) from employee where salary.employeId = E.employeeId).


Here you can see, for each row of outer query the inner query will be executed.

Comments

Post a Comment

Popular posts from this blog

Oracle User vs Oracle Schemas

Oracle schema = Oracle user + database objects owned by that user. When an Oracle user is created using the CREATE USER command, a schema gets created for the user by default with the same name as User There is a one-to-one correspondence between Oracle user name and Oracle schema name. While user = schema in most circumstances, that isn’t true all the time. CREATE SCHEMA does not actually create a schema. It only lets you create multiple tables/views and grant privileges on them in your own schema, in a single transaction. Example:  Here’s an example of using CREATE SCHEMA in the schema “hr” to create a table, create a view on that table, and give grant to another user on that view. SQL> CREATE SCHEMA AUTHORIZATION hr
Read more

Having and Where Clause

Where and Having both are used to apply filter Difference: You can not use Aggregate function in where clause, like select * from employee where SUM(salary) > 5000; but you can use Aggregate function in having clause. But having has to be used in conjunction with " GROUPED BY " clause. GROUP BY, makes the list of group, say if you have applied two " Grouped by" select city,gender   from employee   grouped by city, gender; in above case, you will have below type of  data Group-1 id  name      city      gender salary 2   Dharmu  BLR    M         4000 8   deepak    BLR   ML       5000 Group-2 id  name      city      gender salary 1   rashmi    BLR    F          10000 3   Sargun    BLR...
Read more