Difference between inner join and outer join

An inner join will return a row only if there is a joined row with data in both tables- being joined. An outer join will return a row even if the other table doesn't have a corresponding row.

With an example I will make the scenarios clear. I will create two tables named student and dept. In the student table there will be one department but the corresponding department is not exist in dept table. Now I perform both inner join and outer join on deptid column.

SQL> create table student(stdid number, name varchar2(15),deptid number);
Table created.

SQL> insert into student values (024413,'Rafi',3);
SQL> insert into student values (024101,'Raju',1);
SQL> insert into student values (024434,'Arju',3);

SQL> create table dept(deptid number,deptname varchar2(10));
Table created.

SQL> insert into dept values(2,'EEE');
SQL> insert into dept values(3,'CSE');

SQL>  select s.name,d.deptname from dept d INNER JOIN student s on d.deptid=s.deptid;
NAME            DEPTNAME
--------------- ----------
Rafi            CSE
Arju            CSE

See in case of INNER join rows return only if joined row exist with data in both tables.

SQL> select s.name,d.deptname from dept d RIGHT OUTER JOIN student s on d.deptid=s.deptid;
NAME            DEPTNAME
--------------- ----------
Arju            CSE
Rafi            CSE
Raju

SQL> select s.name,d.deptname from dept d LEFT OUTER JOIN student s on d.deptid=s.deptid;
NAME            DEPTNAME
--------------- ----------
Rafi            CSE
Arju            CSE
                EEE

SQL> select s.name,d.deptname from dept d FULL OUTER JOIN student s on d.deptid=s.deptid;
NAME            DEPTNAME
--------------- ----------
Rafi            CSE
Arju            CSE
                EEE
Raju

As we see in case of OUTER join it return a row even if the other table doesn't have a corresponding row.

Related Documents:
Joins in Oracle