Difference between Inner join and Outer join in SQL

Inner join and outer join are two useful ways to join database tables and query data from multiple tables. In this article, we will look at the difference between inner join and outer join in SQL.


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;

Also read : How to get row_number in MySQL


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;

Also read: How to Get rows not present in another table in MySQL


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;


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.

Also read : How to Get data for every hour in MySQL


Difference between Inner join and Outer join in SQL

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

Inner Join

  • It returns only rows with common column values.
  • You can use INNER JOIN or JOIN clause. Both have same effect
  • It returns null when there are no matching column values between two (or more) tables
  • Inner Join is faster than Outer Join
  • It is useful to do lookups across multiple tables
  • In mathematical terms, it returns intersection of two sets (tables)


Outer Join

  • It returns values from one or more tables even if there is no matching column values
  • For rows with no matching attributes, it returns null for remaining columns
  • Full Outer Join and Full Join have same effect.
  • It is slower than inner join
  • It is used to get complete information in two or more tables
  • Full outer join is not supported in MySQL. It is available in PostgreSQL, SQL Server, Oracle and other popular databases.


Also read: How to do full outer join 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!

mm

About Ubiq

Ubiq is a powerful dashboard & reporting platform. Build dashboards, charts & reports for your business in minutes. Try it for free!