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

SQL Joins in Oracle

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;
— Right outer join / Right Join 
SELECT * FROM table_one t1 
right outer join table_two t2 
on t1.col_one = t2.col_one;

OR   

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

— Full outer join / Full join

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

OR  

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

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

Leave a Comment

Your email address will not be published. Required fields are marked *

shares
Scroll to Top