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;
— 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.
