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.

SiteGround Black Friday Sale Below Banner

Get Free Blogging Tips & Technology updates in your Email !!!

Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

Siteground SALE
70% OFF SiteGround WordPress Hosting – $2.95/month (Exclusive Offer !!!)