Difference Between Inner Join, Cross Join, and Full Outer Join

In this article, we discuss the difference between Inner Join, Cross Join, and Full Outer Join.

• A Join is used to combine two tables, it depends on the relationship between tables.
• Joins are used to retrieve data from multiple tables.
• We join between two or more tables through the primary key and foreign key.

INNER JOIN

• Inner join displays the matching records from two or more tables.
• Inner join applies only the specified columns.
• When a match is not found, it does not return anything.
• Inner Join faster than Full Outer Join. If numerous rows in the tables, there is an index to use.

SYNTAX:-

SELECT column_name(s)

FROM Table_1

INNER JOIN Table_2

ON Table_1.column_name = Table_2.column_name;

CROSS JOIN

• Cross join produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table.
• Cross joins are used to return every combination of rows from two tables, this sometimes called a Cartension product.
• It applies to all columns.

SYNTAX:-

SELECT *

FROM Table_1

CROSS JOIN Table_2;

FULL OUTER JOIN

• Full Outer Join displays the matching or non-matching record of the table.
• When a match is not found, Full Outer Join returns a NULL value.

SYNTAX:-

SELECT *

FROM Table_1

FULL OUTER JOIN Table_2

ON Table_1.column_name = Table_2.column_name;

If you are new to database learning — SQL Server recommended is the following must-watch the video: -