CROSS JOIN & FULL OUTER JOIN

Hi,

Are these two CROSS JOIN & FULL OUTER JOIN resulting same output when

quering ?

Thanks.

No...

Inner Join match the column values on the both Right & Left tables.

Right outer Join match the column values on the Right & Left tables. and unmatched values of Right table

Left outer Join match the column values on the Right & Left tables. and unmatched values of Left table

FULL outer Join match the column values on the Right & Left tables. and unmatched values of Left & Right table

Cross Join never match any column values (there is no ON clause) and the result will produce the Cartesian product.

your statement is correct If and only If you use the following join condition FULL OUTER JOIN ... ON 1=1

Code Snippet

Create Table #tablea (

[Id] int ,

[Name] Varchar(10)

);

Insert Into #tablea Values('1','One');

Insert Into #tablea Values('2','Two');

Insert Into #tablea Values('3','Three');

Insert Into #tablea Values('4','Four');

Insert Into #tablea Values('10','Ten');

Create Table #tableb (

[Id] int ,

[Name] Varchar(10)

);

Insert Into #tableb Values('1','First');

Insert Into #tableb Values('2','Second');

Insert Into #tableb Values('3','Third');

Insert Into #tableb Values('4','Fourth');

Insert Into #tableb Values('5','Fifth');

Insert Into #tableb Values('6','Sixth');

Select * From #tablea A Join #tableb B on A.ID=B.ID

/*

A.IdA.NameB.IdB.Name

-- - -- -

1One1First

2Two2Second

3Three3Third

4Four4Fourth

(4 row(s) affected)

*/

Select * From #tablea A Left Outer Join #tableb B on A.ID=B.ID

/*

A.IdA.NameB.IdB.Name

-- - -- -

1One1First

2Two2Second

3Three3Third

4Four4Fourth

10TenNULLNULL

(5 row(s) affected)

*/

Select * From #tablea A Right Outer Join #tableb B on A.ID=B.ID

/*

A.IdA.NameB.IdB.Name

-- - -- -

1One1First

2Two2Second

3Three3Third

4Four4Fourth

NULLNULL5Fifth

NULLNULL6Sixth

(6 row(s) affected)

*/

Select * From #tablea A Full Outer Join #tableb B on A.ID=B.ID

/*

A.IdA.NameB.IdB.Name

-- - -- -

1One1First

2Two2Second

3Three3Third

4Four4Fourth

NULLNULL5Fifth

NULLNULL6Sixth

10TenNULLNULL

(7 row(s) affected)

*/

Select * From #tablea A Cross Join #tableb B

/*

A.IdA.NameA.IdA.Name

-- - -- -

1One1First

1One2Second

1One3Third

1One4Fourth

1One5Fifth

1One6Sixth

2Two1First

2Two2Second

2Two3Third

2Two4Fourth

2Two5Fifth

2Two6Sixth

3Three1First

3Three2Second

3Three3Third

3Three4Fourth

3Three5Fifth

3Three6Sixth

4Four1First

4Four2Second

4Four3Third

4Four4Fourth

4Four5Fifth

4Four6Sixth

10Ten1First

10Ten2Second

10Ten3Third

10Ten4Fourth

10Ten5Fifth

10Ten6Sixth

(30 row(s) affected)

*/