Difference Between Inner Join and Outer Join in SQL

SQLData StorageData Structure

In this post, we will understand the difference between inner join and outer join in SQL.

Inner Join

  • The clause used is ‘INNER JOIN’ and ‘JOIN’.

  • It returns the combined tuple of two or more tables.

  • When no attributes are common, the result is empty.

  • If the number of tuples is more, then ‘INNER JOIN’ works quickly in comparison to ‘OUTER JOIN’.

  • It is used when detailed information about a specific attribute is required.

  • The ‘JOIN’ and ‘INNER JOIN’ work in the same manner.

Syntax

SELECT *
FROM table_1 INNER JOIN / JOIN table_2
ON table_1.column_name = table_2.column_name;

Outer Join

  • It returns the combined tuple of a specified table.

  • It is returned even when the ‘JOIN’ condition fails.

  • The clauses LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN can be used.

  • It doesn’t depend on the common attributes.

  • If the attribute is blank, NULL is placed instead of the blank.

  • The ‘OUTER JOIN’ is slow in comparison to ‘INNER JOIN’.

  • It is used when complete information is required.

  • FULL OUTER JOIN and FULL JOIN clauses work in the same manner.

Syntax

SELECT *
FROM table_1 LEFT OUTER JOIN / RIGHT OUTER JOIN /
FULL OUTER JOIN / FULL JOIN table_2 ON
Table_1.column_name = table_2.column_name;
raja
Published on 15-Apr-2021 11:14:19
Advertisements