What is the difference between sub query and co-related subquery

Sub Query:
Here inner query gets executed first and based on the result of it the outer query is executed.

This follows BOTTOM_UP approach, first BOTTOM query (inner query) executes and then UPper query (outer query) executes.

Problem Statement: Get all the employees whose department name is ‘A’
Tables are:
Employee_Table: emp_id_col, emp_name_col, emp_sal_col, emp_dept_id_col
Dept_table: dept_id_col, dept_name_col, dept_loc_col
Here dept_id the foreign key

select * from Employee_Table where 
      empt_dept_id_col = 
      (select dept_id_col from Dept_table where dept_name_col='A')

Correlated Sub Query:
I would say this as a kind of SELF JOIN. First outer query executes and then inner query and inner query gets evaluated for each row processed by the outer query.

This follows TOP-BOTTOM approach
Problem Statement: Get the list of employees whose salary is greater than the average salary of every department.
Tables are:
Employee_Table: emp_id_col, emp_name_col, emp_sal_col, emp_dept_id_col

select * from Employee_Table e where emp_sal_col > 
   (select avg(emp_sal_col) from Employee_Table m 
        where e.emp_dept_id_col = m.emp_dept_id_col)

Here in outer query the table alias ‘e’ gets evaluated before the inner query executes.

 

Find nth maximum salary of an employee.

This is another example of co-related sub query and most frequently asked interview question.

emp_id_col emp_name_col emp_sal_col
1 A 100
2 B 200
3 C 300
4 D 400
select emp_sal_col from Employee_Table e1 
  where 
    N-1 
    = 
    (select count(distinct(emp_sal_col)) from Employee_Table e2 
     where e1.emp_sal_col > e2.emp_sal_col)
// Where N is nth highest salary that we want to find.

Explanation:
As correlated subquery operates in top_bottom approach, first outer query executes and then inner query. Here, for every row of outer query, inner query is executed.
Let’s say we want to find 3rd highest salary(i.e, 200)
: First row with employee id ‘1’ and sal ‘100’ from outer query is passed to the inner query. In inner query we check count(distinct(sal)) which is ‘4’ where outerEmployeeTableView.sal > innerEmployeeTableView.sal (it means salaries greater than ‘100’) which is ‘3’. Since N-1, that is (3-1 = 2), does not match we go for next row.
: Second row with employee id ‘2’ and sal ‘200’ from outer query is passed to the inner query. In inner query we check count(distinct(sal)) which is ‘4’ where outerEmployeeTableView.sal > innerEmployeeTableView.sal (it means salaries greater than ‘200’) which is ‘2’. Since N-1, that is (3-1 = 2), matches, outer query returns the salary ‘200’ as output which is 3rd highest salary in the table.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Mawazo

Mostly technology with occasional sprinkling of other random thoughts

amintabar

Amir Amintabar's personal page

101 Books

Reading my way through Time Magazine's 100 Greatest Novels since 1923 (plus Ulysses)

Seek, Plunnge and more...

My words, my world...

ARRM Foundation

Do not wait for leaders; do it alone, person to person - Mother Teresa

Executive Management

An unexamined life is not worth living – Socrates

Diabolical or Smart

Nitwit, Blubber, Oddment, Tweak !!

javaproffesionals

A topnotch WordPress.com site

thehandwritinganalyst

Just another WordPress.com site

coding algorithms

"An approximate answer to the right problem is worth a good deal more than an exact answer to an approximate problem." -- John Tukey