Difference Between Inner and Outer Join

Joins in SQL table are used to combine the data present in two different tables and display the result in a single table. The table which displays the result of join is temporary; the resultant table will be based on the comparison of columns between the tables. The effectiveness of joins are based on the predicate; predicate can be defined as the condition used to join the two tables.

Example for Join

Consider that we have two tables which contains employee names and designation

Employee

Emps id

Emps Name

1001

Tomb

1002

Smirthi

1003

Benga

1004

Anegan

1005

Saranya

1006

Alex Parrish

Designation

Emps id

Emps Design

1001

Team leader

1002

Programmer

1003

Project Leader

1004

Op Manager

1005

C)O

1006

CEO

You may have noted that the designation of the employee, Alex Parrish with the emp id no 106 is not named in the designation table. Also the employee who has the id number 107 is not named in the employee table.

Full Join

Full join applied for the above two tables will match the rows and join the Employee.Emp id = Designation.Emp id predicate. The rows which don’t have any corresponding value are returned with NULL record. Since all rows are used during this join it is called as Full Join. The resultant full join table is given below.

Employee.Emp id

Employee.Emp Name

1001

Tomb

1002

Smirthi

1003

Benga

1004

Angegan

1005

Saranya

1006

Alex Parrish

NULL

NULL

Designation.Emp id

Designation.Emp Design

1001

Team leader

1002

Programmer

1003

Project Leader

1004

Op Manager

1005

COO

NULL

NULL

1006

CEO

Full join is also known as outer join and it can be further classified into left outer join and right outer join.

Difference between Inner and Outer Join

Inner Join select matching rows between two tables. Outer Join select all the rows and records without value are declared as NULL.

Inner Join uses the rows present in the table to match records between two tables

Outer Join uses the columns present in the table to match between two tables.

Various types of keywords are used in outer joins while inner join doesn’t use any keywords to match the records between the tables.

Every row present in two tables will be displayed in the resultant outer join table. The resultant table of inner join contains rows which has matching records.

The resultant table of inner join will display only one record for every matching record. Outer join resultant table will return all the records present in both the tables. The records without corresponding value in outer join table will be returned with NULL value.

Inner joins are used find the similarities between two tables while outer joins are used to find the differences between the tables.