SQL Joins
INNER JOIN
RETURNS Matching records of both the tables
LEFT JOIN / LEFT OUTER JOIN
Inner Join + all the leftover records from LEFT table
RIGHT JOIN / RIGHT OUTER JOIN
Inner Join + all the leftover records in RIGHT table
FULL OUTER JOIN / FULL JOIN
- All records from LEFT table which were not returned from Inner join.
- All records from RIGHT table which were not returned from Inner join. */
SELF JOIN
table join with itself.
NATURAL JOIN
Not recomended
CROSS JOIN
- returns cartisian product.
- Usually used whrn there is a single row in either of the table
To demonstrate the results of the join, the following one column table can be used
DROP TABLE IF EXISTS table3;
create table table3 (id1 int);
DROP TABLE IF EXISTS table4;
create table table4 (id2 int);
insert into table3 values (null),(0),(null),(0),(0),(1),(1);
insert into table4 values (1),(0),(0),(0),(null),(null);
select * from table3;
select * from table4;
Inner Join
Inner join or simply join returns the results for the common id’s between the two tables.
Outer Join
there are 3 types of Outer joins
- Left Outer join or Left join
- Right join
- Full join *
Right and Left Join
Full Join
Full join SQL and Result
Another Example
Inspired from the Stackoverflow link
Inner join
Inner join without equality condition
Outer Join
Left Outer Join
Left Outer join with null check