SQL Server What is the difference between CROSS JOIN and FULL OUTER JOIN

Question

What is the difference between CROSS JOIN and FULL OUTER JOIN in SQL Server   Are they the same  or not  Please explain  When would one use either of these

User · Answer

Cross Join  http   www dba-oracle com t garmany 9 sql cross join htm  TLDR  Generates a all possible combinations between 2 tables  Carthesian product    Full  Outer Join  http   www w3schools com Sql sql join full asp  TLDR  Returns every row in both tables and also results that have the same values  matches in CONDITION

User · Answer

For SQL Server  CROSS JOIN and FULL OUTER JOIN are different  CROSS JOIN is simply Cartesian Product of two tables  irrespective of any filter criteria or any condition  FULL OUTER JOIN gives unique result set of LEFT OUTER JOIN and RIGHT OUTER JOIN of two tables  It also needs ON clause to map two columns of tables   Table 1 contains 10 rows and Table 2 contains 20 rows with 5 rows matching on specific columns  Then CROSS JOIN will return 10 20 200 rows in result set  FULL OUTER JOIN will return 25 rows in result set  FULL OUTER JOIN  or any other JOIN  always returns result set with less than or equal to Cartesian Product number  Number of rows returned by FULL OUTER JOIN equal to  No  of Rows by LEFT OUTER JOIN     No  of Rows by RIGHT OUTER JOIN  -  No  of Rows by INNER JOIN

User · Answer

SQL FULL OUTER JOIN    The FULL OUTER JOIN returns all rows from the left table  table1  and from the right table  table2  irrespective of the match  The FULL OUTER JOIN keyword combines the result of both LEFT OUTER JOIN and RIGHT OUTER JOIN SQL  full outer join is also known as  FULL JOIN   Reference   http   datasciencemadesimple com sql-full-outer-join   SQL CROSS JOIN   In SQL CROSS JOIN Each Row of first table is mapped with the each and every row of second table  Number of rows produced by a result set of CROSS JOIN operation is equal to number of rows in the first table multiplied by the number of rows in the second table  CROSS JOIN is also known as Cartesian product   Cartesian join Number of rows in table A is m  Number of rows in table B is n and resultant table will have m n rows    Reference http   datasciencemadesimple com sql-cross-join

User · Answer

They are the same concepts  apart from the NULL value returned   See below   declare  table1 table  col1 int  col2 int    insert into  table1 select 1  11 union all select 2  22   declare  table2 table   col1 int  col2 int    insert into  table2 select 10  101 union all select 2  202   select     t1        t2   from  table1 t1 full outer join  table2 t2 on t1 col1   t2 col1 order by t1 col1  t2 col1      full outer join col1        col2        col1        col2 ----------- ----------- ----------- ----------- NULL        NULL        10          101 1           11          NULL        NULL 2           22          2           202     select     t1        t2   from  table1 t1 cross join  table2 t2 order by t1 col1  t2 col1      cross join col1        col2        col1        col2 ----------- ----------- ----------- ----------- 1           11          2           202 1           11          10          101 2           22          2           202 2           22          10          101

User · Answer

I d like to add one important aspect to other answers  which actually explained this topic to me in the best way   If 2 joined tables contain M and N rows  then cross join will always produce  M x N  rows  but full outer join will produce from MAX M N  to  M   N  rows  depending on how many rows actually match  on  predicate    EDIT   From logical query processing perspective  CROSS JOIN does indeed always produce M x N rows  What happens with FULL OUTER JOIN is that both left and right tables are  preserved   as if both LEFT and RIGHT join happened  So rows  not satisfying ON predicate  from both left and right tables are added to the result set

User · Answer

One thing that might not always be obvious to some is that a cross join with an empty table  or result set  results in empty table  M x N  hence M x 0   0   A full outer join will always have rows unless both M and N are 0

User · Answer

A cross join produces a cartesian product between the two tables  returning all possible combinations of all rows   It has no on clause because you re just joining everything to everything   A full outer join is a combination of a left outer and right outer join   It returns all rows in both tables that match the query s where clause  and in cases where the on condition can t be satisfied for those rows it puts null values in for the unpopulated fields   This wikipedia article explains the various types of joins with examples of output given a sample set of tables

User · Answer

A full outer join combines a left outer join and a right outer join  The result set returns rows from both tables where the conditions are met but returns null columns where there is no match   A cross join is a Cartesian product that does not require any condition to join tables  The result set contains rows and columns that are a multiplication of both tables

User · Answer

Cross join  Cross Joins produce results that consist of every combination of rows from two or more tables   That means if table A has 3 rows and table B has 2 rows  a CROSS JOIN will result in 6 rows   There is no relationship established between the two tables     you literally just produce every possible combination   Full outer Join   A FULL OUTER JOIN is neither  left  nor  right     it s both  It includes all the rows from both of the tables or result sets participating in the JOIN  When no matching rows exist for rows on the  left  side of the JOIN  you see Null values from the result set on the  right   Conversely  when no matching rows exist for rows on the  right  side of the JOIN  you see Null values from the result set on the  left

User · Answer

Here is an example where both the FULL OUTER JOIN and CROSS JOIN return the same result set without NULL returned  Please note the 1   1 in the ON clause for the FULL OUTER JOIN     declare  table1 table       col1    int     col2    int    declare  table2 table       col1    int     col2    int    insert into  table1 select  1  11   union all select    2  22     insert into  table2 select  10  101 union all select     2  202  select    from     table1 t1 full outer join  table2 t2     on  1   1     2 row s  affected    2 row s  affected  col1        col2        col1        col2 ----------- ----------- ----------- ----------- 1           11          10          101 2           22          10          101 1           11          2           202 2           22          2           202   select    from     table1 t1 cross join  table2 t2    col1        col2        col1        col2 ----------- ----------- ----------- ----------- 1           11          10          101 2           22          10          101 1           11          2           202 2           22          2           202   4 row s  affected

[sql-server] SQL Server: What is the difference between CROSS JOIN and FULL OUTER JOIN?

Examples related to sql-server

Examples related to cross-join

Examples related to full-outer-join