Joins – Left outer, Right outer and full outer

The purpose of SQL join is to combine rows from multiple tables. SQL join is performed by the where clause which combines two or more tables in an SQL statement.

There are four types of joins which described below:

• INNER JOIN or sometimes called SIMPLE JOIN
• LEFT OUTER JOIN or sometimes called LEFT JOIN
• RIGHT OUTER JOIN or sometimes called RIGHT JOIN
• FULL OUTER JOIN or sometimes called FULL JOIN

Left outer join, Right Outer Join and Full Outer Join explained

CREATE TABLE table_one (
col_one NUMBER,
col_two CHAR(1));

CREATE TABLE table_two (
col_one NUMBER,
col_two CHAR(1));

INSERT INTO table_one VALUES (1, 'a');
INSERT INTO table_one VALUES (2, 'b');
INSERT INTO table_one VALUES (3, 'c');

INSERT INTO table_two VALUES (2, 'B');
INSERT INTO table_two VALUES (3, 'C');
INSERT INTO table_two VALUES (4, 'D');
commit;

— Inner Join or Simple Join

SELECT * FROM table_one t1
INNER JOIN table_two t2
ON t1.col_one = t2.col_one;

— Left outer join / Left join

SELECT * FROM table_one t1 
left outer join table_two t2 
on t1.col_one = t2.col_one;

OR  

SELECT * FROM table_one t1 
left join table_two t2 
on t1.col_one = t2.col_one;

If there is any confusion about SQL Join, don’t forget to comment.