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: -
