what is the difference between sunquery and correlatted subquery?

Question

I was asked this question in one of the interview on php

Answer 1

I think you mean "subquery" and a "correlated subquery".

Subquery:
select employee_name
from employees
where emp_no in (select distinct emp_no from late_attendance)

Correlated Subquery:
select e1.emp_no, e1.dept_cd, u.active_cd
from user_profile u, employee e1
where u.emp_no=e1.emp_no
and e1.effective_date = (select max(effective_date) from employee e2 where e1.emp_no=e2.emp_no)

Notice in the first query, the inner query (the sub query) runs independant of the outer query. Conversely, in the correlated subquery, the inner query requires information from the outer query in order to run (e1.emp_no = e2.emp_no).

To help understand why this would be done, assume that the employee table inserts a new row with a new effective date each time a change is made. Thus, obtaining the latest effective dated record.