Learning Self Join!

-



SELF JOIN

 Usage:
HR manager to data analyst: Bring me the employee ids along with the name of manager managing that employee. Data analyst runs below query to fetch the required data.

 SELECT E.EMPLOYEE_ID, M.EMPLOYEE_ID AS MANAGER_ID, M.FIRST_NAME, M.LAST_NAME FROM EMPLOYEES E, EMPLOYEES M
WHERE E.MANAGER_ID= M.EMPLOYEE_ID;








Comments

Post a Comment

Popular posts from this blog

Let's learn Right Outer Join!

-
Image
RIGHT OUTER JOIN (i)  HR Manager wants all the employees’ details regardless of whether they have past Job-history or not. If employee has a job-history then manager also wants job-history details. Manager to data analyst: Bring me the details of employees along with the details of their job history if employee has one. Data analyst runs below query to fetch the required data. SELECT * FROM JOB_HISTORY JH RIGHT OUTER JOIN EMPLOYEES E ON E.EMPLOYEE_ID= JH.EMPLOYEE_ID;   (ii) HR manager wants details of departments regardless of department having manager or not. If department has manager then HR manager also wants the details of manager.  Manager to data analyst:  Bring me the details of departments along with the details of manager of the department. Data analyst runs below query to fetch the required data. SELECT * FROM EMPLOYEES E RIGHT OUTER JOIN DEPARTMENTS D ON D.MANAGER_ID= E.EMPLOYEE_ID;
Read more

Let's learn Cross Join!

-
Image
CROSS JOIN HR manager to data analyst: Bring me the every possible combination of department ids and job ids along with the details for both. Data analyst runs below query to fetch the data.  SELECT * FROM DEPARTMENTS, JOBS; 
Read more

Learning the Natural Join

-
Image
NATURAL JOIN Usage:  HR Manager to data analyst: Bring me the details of employees and the details of corresponding departments. Data analyst runs below query to fetch the required data. SELECT * FROM EMPLOYEES NATURAL JOIN DEPARTMENTS;
Read more