Join, Right Join/Left join, Full Join, Natural Join, Cross Join

Basics  

Left Join - show all in the left table and correspondent records in right table;

Full join - (Left Join + Right Join)

Natural Join - Auto-link 2 tables with same column names and then do inner join.

Cross Join : Join with Cartesian Product.

Join/Inner join : Only show the intersection of 2 tables , null will not be shown.

Ok , For the basic concept ,I don't want to further to much in . however I will analyze one question when I was doing my OCP exam in order for you to totally understand how it works:

 OCP 1Z0 051 Question 126

126. View the Exhibit and examine the data in the PROJ_TASK_DETAILS table. 
The PROJ_TASK_DETAILS  table  stores  information about  tasks  involved  in a project and  the  relation between them. The  BASED_ON  column  indicates  dependencies  between  tasks.  Some  tasks  do  not  depend on  the completion of any other tasks.

You need to generate a report showing all task IDs, the corresponding task ID they are dependent on, and the name of the employee in charge of the task it depends on.

Which query would give the required result?  

A. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.based_on = d.task_id); 
B. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on = d.task_id); 
C. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p FULL OUTER JOIN proj_task_details d ON (p.based_on = d.task_id); 
D. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.task_id = d.task_id); 

This question is quite challenging for me when i sat there doing the exam years agos. Let's start from the easiest option and work it out: 

CREATE OR REPLACE VIEW proj_task_details
(task_id, based_on,task_in_charge) AS
SELECT 'P01',NULL,'KING' FROM dual UNION ALL
SELECT 'P02','P01','KOCHAR' FROM dual UNION ALL
SELECT 'P03',NULL,'GREEN' FROM dual UNION ALL
SELECT 'P04','P03','SCOTT' FROM dual;

D is wrong because the linking criteria p.task_id = d.task_id is wrong, it equals that you directly fetch data from a single table, no change at all, Yes, you may say that it indeed show the result the question mentioned. however, this is report generation, no extra data is allowed. you notice that TASK_IN_CHARGE column has something extra. that's where it goes wrong;

SQL> SELECT p.task_id, p.based_on, d.task_in_charge
  2    FROM proj_task_details p
  3    JOIN proj_task_details d
  4      ON (p.task_id = d.task_id);
TASK_ID BASED_ON TASK_IN_CHARGE
------- -------- --------------
P01              KING
P02     P01      KOCHAR
P03              GREEN
P04     P03      SCOTT
4 rows selected

A Inner Join/Join can't show data with based_on null; null will not be shown as the basic says. so some rows will go missing in the final result.

SQL> SELECT p.task_id, p.based_on, d.task_in_charge
  2    FROM proj_task_details p
  3    JOIN proj_task_details d
  4      ON (p.based_on = d.task_id);
TASK_ID BASED_ON TASK_IN_CHARGE
------- -------- --------------
P02     P01      KING
P04     P03      GREEN
2 rows selected

C Full Join will show the matched data in one row. And show the mismatched data in another rows. =(Left Join + Right Join) which means it will show all related/unrelated stuff out  which definitly is not what we want.

SQL> SELECT p.task_id, p.based_on, d.task_id, d.task_in_charge
  2    FROM proj_task_details p
  3    FULL OUTER JOIN proj_task_details d
  4      ON (p.based_on = d.task_id)
  5   ORDER BY 1, 3;
TASK_ID BASED_ON TASK_ID TASK_IN_CHARGE
------- -------- ------- --------------
P01                      
P02     P01      P01     KING
P03                      
P04     P03      P03     GREEN
                 P02     KOCHAR
                 P04     SCOTT
6 rows selected

B Left join, it will show all the data from the table on the left with task_id, And if the table on the right without data matched, it will display null, which is the blank above P01 and P03 in the BASED_ON column. Alright This is the report format we want.

SQL> SELECT p.task_id, p.based_on, d.task_in_charge
  2    FROM proj_task_details p
  3    LEFT OUTER JOIN proj_task_details d
  4      ON (p.based_on = d.task_id)
  5   ORDER BY 1;
TASK_ID BASED_ON TASK_IN_CHARGE
------- -------- --------------
P01              
P02     P01      KING
P03              
P04     P03      GREEN
4 rows selected

I hope through this question , you can totally know how to use different joins and use it flexibly in your work:)