Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-10-07 : 07:43:33
|
| Anand writes "Hi,I have a clear idea about UNION and Full outer Join in SQL Server. My query is, how these two statement brings out result with more tables and how build and probe input will identify the tables in HASH JOIN.Regards,S. Anand" |
|
|
1fred
Posting Yak Master
158 Posts |
Posted - 2002-10-07 : 16:33:27
|
| The UNION Statement will retrieve all the lines from both tables. If in table A you have 10 lines and in table B you have 30 lines, it will return 40 lines. But in full outer join, you will only get 30 lines, and also in full outer join, you need a join on the 2 tables. |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-10-07 : 18:06:55
|
| 1fred, if there are any duplicates in the values you are selecting between table A and table B, then you won't get 40 lines with a UNION, but rather only the distinct lines.If you do UNION ALL you will then get 40 lines. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-10-07 : 18:08:37
|
| And, at the risk of sounding stupid, would somebody tell me what "build and probe input" is? This is the second post where S. Anand uses that term. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-07 : 18:13:30
|
| I have a feeling it's in reference to another (more obscure) database product and how it processes JOINs. Or, it could be that hash tables and/or hashing algorithms might use that kind of terminology. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-07 : 21:26:53
|
| There is a product called probe (I think) which claims to do performance checks on all your servers and it also looks at query plans.As all these products it will suffer from not knowing what is important to optimise but...Much better to learn about these things yourself and get to know the system.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-10-08 : 04:13:24
|
| "Build and probe" is definitely referring to hash joins. You build the hash using rows in one table and probe it with rows from the other.There are circumstances where a full outer join and a union of two tables will return the same rows -- when the join condition is equality on a candidate key, vs a union that includes a candidate key. In these circumstances, SQL Server can produce very similar execution plans. But usually, the result columns for the full outer join need to use COALESCE to get a non-null value from either table, which gets messy.Quite what these two things have to do with each other I've no idea.Edited by - Arnold Fribble on 10/08/2002 04:15:23 |
|
|
|
|
|
|
|
|
Difference between UNION and FULL outer join