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