What is SQL Joins | Difference between “INNER JOIN” and “OUTER JOIN”?

What is SQL Joins?

Joins concept in SQL programming to combine two table or more than two table based on their column. Some times joins used to combine single column based on alias to get some desire result for requirement. Joins used to get data retrieval based on requirement. Basically joins are four types.

There are four types of SQL Joins.

1. Inner join.
2. Right Join or right outer join
3. Left Join or left outer join
4. Full Join or full outer join.

A Full Picture which will give you exact idea about SQL Joins:

Concept of SQL Joins:

• An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.

• An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Let’s Consider a below tables to have an example.
Table -A
—————
10
20
30
40
Table B
————
30
40
50
60

Please Note that below following main points.

1. (10,20) are unique in table -A, [If compare from table-B]
2. (30,40) are common into both table -A & B.
3. (50,60) are unique into table -B. [If compare from table-A]

Create a table A to demonstrate the Joins.

create table A (value number (10));
insert into A values (10);
insert into A values (20);
insert into A values (30);
insert into A values (40);

result set A

Create a table B to demonstrate the Joins.

create table B (value number (10));
insert into B values (30);
insert into B values (40);
insert into B values (50);
insert into B values (60);

Inner join

Inner Join will gives the intersection records -commonly available in both table. An example in above case two rows are common.
(30,40) are common into both table -A & B.

select * from A INNER JOIN B on A.value = B.value;
select a.*, b.* from a,b where A.value = B.value;

Import Notes:
case 1. if we select records from A.*

case -02 if we select records from B table.

Left outer join

the result of a left outer join will give all rows in A, plus any common rows in B. please look the result set of below.

select * from A LEFT OUTER JOIN B on a.value = b.value order by 1 asc;
select A.*, B.* from A,B where a.value = b.value(+) order by 1 asc;

case -01 . If select data from table A ?

case -02 If select data from table B?

Right outer join.

The result of a right outer join will give all rows in B, plus any common rows in A. please look the result set of below.

select * from A right OUTER JOIN B on a.value = b.value order by 1 asc;
select A.*, B.* from A,B where a.value(+)= b.value order by 1 asc;

case -01 . If select data from table A ?

case -02 If select data from table B?

Full outer join

the result set of a full outer join will give you the union of table A and table – B,
An example – All the rows in table- A and all the rows in table-B. (it would be union)
If incase table -A doesn’t have a corresponding data exist in column -Value in table B in column-value, then the table -B portions is null, and vice versa. please have a results.

select * from A FULL OUTER JOIN B on a.value = b.value order by 1 asc;

case -01. Data from table A.

case -02. Data from table B.