Inner join vs Outer join in SQL

Last updated on September 26th, 2024 at 09:10 am

Often, database programmers and software developers need to retrieve data from several tables at once, using a single query. For such cases, database systems provide a feature known as SQL JOINS, which allows you to specify the tables you need to be connected while executing your query. Every database supports inner joins and outer joins. There are several types of outer joins while there is only one inner join. In this article, we will learn the difference between inner join and outer join in SQL.

What are Joins in SQL

Joins allow you to fetch data from multiple tables using one or more common columns. Inner Joins and Outer Joins are the two most common types of joins. Inner joins returns rows based on column values that are common to all tables. Outer Joins also return rows that are otherwise returned by inner join and also those rows that are present in one of the other tables.

What is Inner Join

Inner join returns rows from two or more tables where at least one column has same value in all the tables. If there are no common values, then no rows are returned.

Here is the syntax of inner join:

select *  from table1 INNER JOIN table2 
on table1.column_name = table2.column_name;

OR

select * from table1 JOIN table2 
on table1.column_name = table2.column_name;

Please note, while using inner joins, both tables need to have the common column names used in the JOIN condition. Whether you use INNER JOIN or JOIN in your SQL query, the result remains the same.

Equi Join

There is also something called an EQUI JOIN which is similar to INNER JOIN but has some important differences. Here is the syntax of equi join.

select *
  from table1 T1, table2 T2
  where T1.column_name1 = T2.column_name2

Inner Join vs Equi Join

In INNER JOIN, we do a cross product between two tables to combine all records, where a certain condition is met. In EQUI JOIN, we specifically use equality operator to join tables. In INNER JOIN, the joining condition may be equality or non equality (greater than, less than, not equal to, between, etc.).

In INNER JOIN, the joining is done strictly based on common column names in both tables. In EQUI JOIN, there is no such requirement. In your condition, you can specify joins between any two columns of the two tables.

What is Outer Join

Outer join returns rows from two or more tables where at least one column has common value, as well as rows where the join condition fails. There are 3 types of outer joins:

Left Outer Join

In this case, all rows of left table are retained in the result, in case its join conditions match with other tables, their column values are returned, else null values are returned for those columns. Here is the syntax of left outer join.

select * from table1 
LEFT OUTER JOIN table2 
on table1.column_name = table2.column_name;

In the above query, all rows of table1 are retained in the result.

Right Outer Join

In this case, all rows of right table are retained in the result, in case its join conditions match with other tables, their column values are returned, else null values are returned for those columns.

Here is the syntax of right outer join

select * from table1 
RIGHT OUTER JOIN table2 
on table1.column_name = table2.column_name;

In the above query, all rows of table2 are retained in the result.

Full Outer Join

In this case, all rows of both tables are returned. If join condition is satisfied, columns are populated using values of both tables. If join condition fails, then values of one of the tables are populated in columns and the rest are populated as nulls. Here is its syntax.

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

Please note, full outer joins can return lot of rows in result.

Difference between Inner join and Outer join in SQL

Here are the key differences between inner join vs outer join.

INNER JOIN	OUTER JOIN
It returns only rows with common column values.	It returns values from one or more tables even if there are no matching column values
You can use INNER JOIN or JOIN clause. Both have same effect	Full Outer Join and Full Join have same effect.
There are no variations in INNER JOIN	Outer Joins can be LEFT OUTER JOIN, RIGHT OUTER JOIN or FULL OUTER JOIN
Common columns or fields are required. They work best if tables are related	Common columns or fields are not required. Tables need not be related.
Inner Join returns rows less than or equal to either tables.	Left Outer Join returns number of rows equal to that in left table. Right Outer Join returns number of rows equal to that in right table. Full Outer Join returns both tables.
It returns null when there are no matching column values between two (or more) tables	For rows with no matching attributes, it returns null for remaining columns
Inner Join is faster than Outer Join	It is slower than inner join
It is useful to do lookups across multiple tables	It is used to get complete information in two or more tables
In mathematical terms, it returns intersection of two sets (tables)	In mathematical terms, full outer join is a union of two sets
Inner Join is supported by every database system	Full outer join is not supported in MySQL. It is available in PostgreSQL, SQL Server, Oracle and other popular databases.

Conclusion

In this article, we have learnt about the different kinds of SQL joins available in database systems. We have learnt what INNER JOINS and OUTER JOINS are and about their different types. We have also learnt about the key difference between Inner joins and Outer joins. Depending on your use case, it is important to use the right type of join to get accurate results in shortest possible time. Using a wrong join will most likely give you wrong result. Before you run an SQL join on the full tables, it is advisable to run them on a shorter version of your tables obtained by using LIMIT clause. This will save you a lot of time, especially if you have large tables.

Also read:
How to do full outer join in MySQL
How to get row_number in MySQL
How to Get data for every hour in MySQL
How to Get rows not present in another table in MySQL

Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!