What is the difference between INNER JOIN and OUTER JOIN in SQL?

In a relational database, the data are distributed into different tables to reduce data redundancy and improve data integrity. The JOINs in SQL help to combine the rows of two or more tables using one or more related columns.

JOINs in the SQL can be broadly categorized into two groups:
1. INNER JOIN
2. OUTER JOIN

There are three types of OUTER JOINs in SQL.

• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

INNER JOIN

The INNER JOIN between two tables returns all the rows which are present in both the tables. The concept of JOIN in SQL can be visualized using Venn diagrams. In the below Venn diagram, the circles represent the tables, and the intersection between those circles represents the INNER JOIN’s output.

Let us consider the two tables, ‘table1’ and ‘table2’.

CREATE TABLE table1(ID INT);
CREATE TABLE table2(ID INT);

INSERT INTO table1
VALUES('1'),
('2'),
('3'),
('4');

INSERT INTO table2
VALUES('3'),
('4'),
('5'),
('6');

In these tables, IDs 3, 4 are common in both of the tables. So, INNER JOINing these tables only returns IDs 3 and 4 and discards all the other IDs.

SELECT t1.ID AS table1, t2.ID AS table2
FROM table1 t1
INNER JOIN table2 t2
ON t1.id = t2.id;

-- The keyword INNER is optional in the INNER JOIN. So, we can only write JOIN
SELECT t1.ID AS table1, t2.ID AS table2
FROM table1 t1
JOIN table2 t2
ON t1.id = t2.id;

-- Both queries return same output
#Output
table1.ID table2.ID
----------- -----------
3 3
4 4

LEFT OUTER JOIN

The LEFT OUTER JOIN returns all the rows from the left table and the rows from the right table, matching with the left table. The below diagram shows that the LEFT OUTER JOIN returns all values from the left table and both tables’ common values.

We use LEFT OUTER JOIN when we need all the values from the left table, but the values from the right table are optional.

SELECT t1.ID AS [table1.ID],
t2.ID AS [table2.ID]
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON t1.id = t2.id;


-- In LEFT OUTER JOIN, the keyword OUTER is optional and we generally write only LEFT JOIN
SELECT t1.ID AS [table1.ID],
t2.ID AS [table2.ID]
FROM table1 t1
LEFT JOIN table2 t2
ON t1.id = t2.id;

-- Output
table1.ID table2.ID
----------- -----------
1 NULL
2 NULL
3 3
4 4

In the above query, the output has a NULL value in the [table2.ID] column. It is because table “table2” does not have IDs 1 and 2. So, for any missing rows in the right table, a NULL value will be returned.

RIGHT OUTER JOIN

The RIGHT OUTER JOIN is the same as LEFT OUTER JOIN except that all the right tables’ data are returned along with the common data.

We use RIGHT OUTER JOIN in the cases where we need all the values from the right table, but the left table’s values are optional.

SELECT t1.ID AS [table1.ID],
t2.ID AS [table2.ID]
FROM table1 t1
RIGHT OUTER JOIN table2 t2
ON t1.id = t2.id;
-- In RIGHT OUTER JOIN, the keyword OUTER is optional and we generally write only RIGHT JOIN
SELECT t1.ID AS [table1.ID],
t2.ID AS [table2.ID]
FROM table1 t1
RIGHT JOIN table2 t2
ON t1.id = t2.id;
-- Output
table1.ID table2.ID
----------- -----------
3 3
4 4
NULL 5
NULL 6

FULL OUTER JOIN

The FULL OUTER JOIN returns all rows of both the tables regardless of whether there are any matching rows or not. It is the combination of LEFT OUTER JOIN and RIGHT OUTER JOIN. In the below diagram, all the values of both circles are selected for FULL OUTER JOIN.

SELECT t1.ID AS [table1.ID],
t2.ID AS [table2.ID]
FROM table1 t1
FULL OUTER JOIN table2 t2
ON t1.id = t2.id;
-- In FULL OUTER JOIN, the keyword OUTER is optional and we generally write only FULL JOIN
SELECT t1.ID AS [table1.ID],
t2.ID AS [table2.ID]
FROM table1 t1
FULL JOIN table2 t2
ON t1.id = t2.id;
-- Output
table1.ID table2.ID
----------- -----------
1 NULL
2 NULL
3 3
4 4
NULL 5
NULL 6

In MySQL, FULL OUTER JOIN is not supported. We can use the output of both the LEFT JOIN and the RIGHT JOIN and use the UNION operator to combine their outputs to simulate the FULL OUTER JOIN.

SELECT t1.ID AS [table1.ID],
t2.ID AS [table2.ID]
FROM table1 t1
LEFT JOIN table2 t2
ON t1.id = t2.id

-- Remember to use the UNION operator, not the UNION ALL. If we use UNION ALL, the row of ID -- 3 and 4 will be duplicate.
UNION
SELECT t1.ID AS [table1.ID],
t2.ID AS [table2.ID]
FROM table1 t1
RIGHT JOIN table2 t2
ON t1.id = t2.id;


-- Output
table1.ID table2.ID
----------- -----------
NULL 5
NULL 6
1 NULL
2 NULL
3 3
4 4

Up Next:

Read How to add an IDENTITY to an existing column in SQL?