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

Inside Group by

Group by <SameColumn>, this basically  group the rows having same column value. lets say, --> Group by department, this this will club all the rows which has same department, and it will have some sort of list of list, 1st position, list of 6 rows, from CSE department 2nd row,      list of 2 rows from ECE department Now in these rows, you can only perform some aggregate funtion and display it, you can display employee name of department as output, because one collection, say 1st position, may have 6 rows each having different employee name, what it will display? i.e. you should/can perfrom only agregate function on this grouped by list. select count(CustomerID) as countryhead,country,city from customers group by Country,city order by countryhead DESC; countryhead Country City 6 UK London 5 Mexico México D.F. 4 Brazil São Paulo 3 Argentina Buenos Aires 3 Brazil Rio de Janeiro 3 Spain Madrid
Read more