In simple words :
Inner join -> Take ONLY common records from parent and child tables WHERE primary key of Parent table matches Foreign key in Child table.
Left join ->
pseudo code
1.Take All records from left Table
2.for(each record in right table,) {
if(Records from left & right table matching on primary & foreign key){
use their values as it is as result of join at the right side for 2nd table.
} else {
put value NULL values in that particular record as result of join at the right side for 2nd table.
}
}
Right join : Exactly opposite of left join . Put name of table in LEFT JOIN at right side in Right join , you get same output as LEFT JOIN.
Outer join : Show all records in Both tables No matter what. If records in Left table are not matching to right table based on Primary , Forieign key , use NULL value as result of join .
Example :
Lets assume now for 2 tables
1.employees , 2.phone_numbers_employees
employees : id , name
phone_numbers_employees : id , phone_num , emp_id
Here , employees table is Master table , phone_numbers_employees is child table(it contains emp_id as foreign key which connects employee.id so its child table.)
Inner joins
Take the records of 2 tables ONLY IF Primary key of employees table(its id) matches Foreign key of Child table phone_numbers_employees(emp_id).
So query would be :
SELECT e.id , e.name , p.phone_num FROM employees AS e INNER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;
Here take only matching rows on primary key = foreign key as explained above.Here non matching rows on primary key = foreign key are skipped as result of join.
Left joins :
Left join retains all rows of the left table, regardless of whether there is a row that matches on the right table.
SELECT e.id , e.name , p.phone_num FROM employees AS e LEFT JOIN phone_numbers_employees AS p ON e.id = p.emp_id;
Outer joins :
SELECT e.id , e.name , p.phone_num FROM employees AS e OUTER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;
Diagramatically it looks like :
