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)
*/
No comments:
Post a Comment