Why are SQL results different?
Why are the following queries returning different result sets?
select count(ml.link_type),mc.conv_string
from MSP_CONVERSIONS mc,MSP_LINKS ml
where ml.PROJ_ID = 4
and mc.STRING_TYPE_ID = 3
and mc.CONV_VALUE *= ml.link_type
group by mc.conv_string
select count(ml.link_type),mc.conv_string
from MSP_CONVERSIONS mc left outer join MSP_LINKS ml on mc.CONV_VALUE = ml.LINK_TYPE
where ml.PROJ_ID = 4
and mc.STRING_TYPE_ID = 3
group by mc.conv_string
The first query returns:
3 FF
10790 FS
0 SF
117 SS
The second query returns:
3 FF
10790 FS
117 SS
Both queries run against a SQL Server 2008 Standard database. I can't figure out why two different result sets are being returned? I thought * = was the shorthand syntax for LEFT OUTER JOIN. I looked at this for so long, maybe I missed something small?
Thanks...
Since your first request is indeed equivalent to this:
select count(ml.link_type),mc.conv_string
from MSP_CONVERSIONS mc
LEFT JOIN MSP_LINKS ml
ON ml.PROJ_ID = 4
and mc.STRING_TYPE_ID = 3
and mc.CONV_VALUE = ml.link_type
group by mc.conv_string
You have moved all the conditions into the join so that it is not possible to completely filter out any rows from the table MSP_CONVERSIONS
. It is best to stick to the full "LEFT / INNER JOIN" syntax and avoid confusion.
a source to share
"* =" is not so much the "shorthand" syntax as it is the pre-ANSI OUTER JOIN syntax. Don't use it. Also, in general, if you have "LEFT OUTER JOIN b ..." in your selection, then adding extra criteria to "b" in the WHERE clause is a bad idea - if it reads like filtering to apply to the result from the join then it will discard any lines that did not match in b --- effectively converting your outer join to an inner join.
This is related to what Joel wrote. All conditions in the "ON" clause mean that filtering is applied at join time and that a different result. The ANSI syntax is more explicit.
a source to share