Difference between Cross Join & Full Outer Join

Difference between Cross Join & Full Outer Join

In SQL Server, there are many types of Joins with which data can be matched and can produce desired results. In this post, we will be differentiating between two SQL server Joins - Cross Join & Full Outer Join.

Cross Join: It gives a result which is equal to the no. of rows in the one table multiplied by the no. of rows in the other table (if there is no WHERE clause used). This type of result set is named as Cartesian Product. CROSS JOIN performs like an INNER JOIN if WHERE clause is used.

There is an alternative way of retrieving the same result set by giving column names (comma (‘,’) separated) in SELECT statement and mentioning the names of table after the FROM clause.

Syntax of CROSS JOIN:

Select *  
FROM table_1  
CROSS JOIN table_2;

Example of CROSS JOIN:

In this example, two tables are taken from Northwind database for example Orders & Employees.

SELECT * FROM Orders;


It returns 830 rows.

SELECT * FROM Employees


It returns 9 rows.

Now, if we execute CROSS JOIN command, it will return the Cartesian Product of both tables and result into 7470 rows (830 x 9).

Select *  
FROM Orders  
CROSS JOIN Employees;

Full Outer Join: Unlike Inner join, Outer join returns all the rows that meets WHERE or HAVING condition from at least one table taken in the FROM clause of SELECT statement. Left outer join retrieves all the rows from left table referenced in SELECT command, and a Right outer join retrieves all the rows from the right table referenced in SELECT command.

Full Outer Join returns all rows from both the tables (i.e. left & right). By using a Full Outer Join in SELECT command, one can retain the non-matching information by considering non-matching rows in the results of a join.

Syntax of Full Outer Join:

SELECT Table_1_Alias.*,Table_2_Alias.* 
FROM Table_1 Table_1_Alias
FULL OUTER JOIN Table_2 Table_2_Alias
ON Table_1_Alias.Table_1_Matching_Column = Table_2_Alias.Table_2_Matching_Column;

Example of Full Outer Join:

In this example, 2 tables are taken from Northwind database for example Customers & Orders.

SELECT * FROM Orders;

It has 830 rows.

SELECT * FROM Customers;


It has 91 rows.

Let’s execute SELECT command with LEFT OUTER JOIN first, then RIGHT OUTER JOIN and in the end FULL OUTER JOIN.

Left outer join

SELECT c.*,o.* FROM Customers c
LEFT OUTER JOIN Orders o
ON c.CustomerID=o.CustomerID;

It returned 832 rows, because Customers (Left) Table has two customer ids which are not there in Orders (Right) Table.

Right outer join

SELECT c.*,o.* FROM Customers c
RIGHT OUTER JOIN Orders o
ON c.CustomerID=o.CustomerID

It returned 830 rows, because Customers (Left) Table has 2 customer ids which are not there in Order (Right) Table. So, result set referenced RIGHT OUTER JOIN, so it ignored extra Customer IDs which are not in Orders (Right) Table.

Full outer join

SELECT c.*,o.* FROM Customers c
FULL OUTER JOIN Orders o
ON c.CustomerID=o.CustomerID

By using a Full Outer Join in SELECT command, it retained the non-matching information (Customer IDs) by considering non-matching rows (No Corresponding data in Orders Table) of both the tables.