SQL Standard

SQL Inner vs Outer Join

There are two major joins in SQL: inner and outer join. Although there are various variations of the two join types, they all stem from these two.

Let us discuss what they are, how they work, and the significant differences.

SQL Inner Join

An SQL Inner join is a type of SQL join that returns a new table by combining the records between both tables. The inner join query compares each row from each table and finds all the matching pairs. If the row corresponds to the specified condition, the query combines them into a resulting row.

We can express the syntax of an SQL inner join as shown:

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

SQL Outer Join

The second type of joins in SQL is Outer joins. An outer join returns all the records whether it finds any matching records in either table.

An outer join returns the records even if the condition fails.

There are 3 types of outer joins:

  1. Left outer join
  2. Right outer join
  3. Full outer join

Left Outer Join
A left outer join returns all the records from the left table and only the matching records from the right table.

The syntax is as shown below:

SELECT column_name(s)
FROM table1
LEFT JOIN Table2
ON Table1.Column_Name=table2.column_name;

Right Outer Join
Similarly, the right outer join returns all the records from the right table and only the matching records from the left table.

The syntax is expressed as below:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Full Outer Join
This type of join returns all the records when a match is in either table. The syntax is as shown:

SELECT column_name
FROM table1
FULL OUTER JOIN table2
ON table1.columnName = table2.columnName
WHERE condition;

Differences between Inner and Outer Join

The following are the major differences between inner and outer joins in SQL.

  1. An inner join returns the matching records between the tables.
  2. An inner join has only one join variation.
  3. An outer join has three variations: outer join, inner join, and full join.
  4. In most cases, an inner join generally outperforms an outer join

Closing

This article outlines the fundamental difference between an inner and outer join in SQL.

Thanks for reading!!

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list