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.

SQL: Inner vs Outer join

Example for Join

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

Employee

Designation

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

The SQL for the full join between two tables is given below.

select * from Employee full join Designation
on Employee.Emp id = Designation.Emp id;

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.

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

• What is Meant by Inner and Outer Joins?

What is Inner Join?

Inner join can be used to return the matching rows between the two tables. The following SQL can be used to inner join the two example tables.

select * from employee inner join location on
Employee.Emp id = Designation.Emp id

The resultant table for the above inner join SQL is given below.

The SQL for inner join applied using WHERE clause is given below

select * from Employee1, Designation
where Employee1.Emps id = Designation.Emps id

• Learn: Aptitude questions and answers

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.