SQL CROSS JOIN Vs FULL OUTER JOIN Statement

There are several sorts of Joins in SQL Server that may be used to match data and achieve desired results.

We'll distinguish between two SQL server joins in this post: CROSS JOIN and FULL OUTER JOIN.

SQL CROSS JOIN Statement

It yields a result equal to the number of rows in one table times the number of rows in the other table (if there is no WHERE clause used).

Cartesian Product is the name given to this type of result set.

When the WHERE clause is used, the CROSS JOIN behaves like an INNER JOIN.

Giving column names in the SELECT statement and stating table names after the FROM clause is an alternative approach of receiving the same result set.

Example 1: Implement Cross join Statement

We will create the two-sample tables which in Book and Author .In this both table we have same column id compare these two .

Mysql, Sql-server, Sqlite

Run below query ,

Select *  
FROM AB_Author
CROSS JOIN AB_Book Where AB_Author.aid = AB_Book.aid;

Output: The result of above query is ,

SQL FULL OUTER JOIN Clause

Unlike an inner join, an outside join retrieves all rows from at least one table in the FROM clause of a SELECT statement that fulfil the WHERE or HAVING conditions.

A Left outer join returns all rows from the left table specified in the SELECT query.

whereas a Right outer join returns all rows from the right table specified in the SELECT instruction.

All rows from both tables are returned in a Full Join . Non-matching information can be retained by using a Full Join in the SELECT query and considering non-matching rows in the join result.

Example: We have two table Author and Book table to analyze left,right,full join clauses

Loot at second table Book

Execute SELECT command with LEFT JOIN.

Mysql,Sql-server,Sqlite

SELECT Author.AuthorName,Author.AID,Book.BookName,Book.AID
FROM Author
LEFT JOIN ABook
ON Author.AID=Book.AID;

Output: The output will be

Execute SELECT command with RIGHT JOIN .

Mysql,Sqlite,Sql-server

SELECT Author.AuthorName,Author.AID,Book.BookName,Book.AID
FROM Author
RIGHT JOIN Book
ON Author.AID=Book.AID;

Output: The output will be

Execute SELECT command with FULL JOIN .

SELECT Author.AID,Book.AID
FROM Author
FULL JOIN Book
ON Author.AID = Book.AID;

SQL Difference Between CROSS JOIN And FULL JOIN Clause

There are 5 main contrast in cross join & full join :-