February 12, 2009 at 3:01 am
the first sql:
select
item_receipts.item_number,
item.item_desc,
sum(delivery_qty) as total_receipts,
sum(returns_qty) as total_returns,
sum(m_issue) as m_issue
from item_receipts
left outer join item
on item_receipts.item_number = item.item_number and item_receipts.bonded='b'
group by item_receipts.item_number, item.item_desc
order by item_receipts.item_number
and the second sql:
select
item_receipts.item_number,
item.item_desc
sum(delivery_qty) as total_receipts,
sum(Returns_qty) as total_returns,
sum(m_issue) as m_issue
from item_receipts, item
where item_receipts.item_number *= item.item_number
and item_receipts.bonded='B'
group by item_receipts.item_number, item.item_desc
order by item_receipts.item_number
the first return 16389 records and second is 10679,
what's the difference of the two sql?
Any assistance will be appreciated.
February 12, 2009 at 3:25 am
Probably to do with NULLs and the way they get interpreted by the WHERE clause rather than the JOIN clause.
See http://www.sqlservercentral.com/articles/Basic+Querying/ansijoins/189/ in the section entitled 'Outer Join Gotchas'
Kev
February 13, 2009 at 9:34 am
FWIW,
I tend to avoid *= like the plague.
Although more verbose, "LEFT OUTER JOIN" is, in my opinion, much easier to follow when reading a long script.
I would say, if you have to debug one such script, it would be worthwhile to replace the "*="statements.
Of course, if you are stuck with legacy code, yeah, some time ago there were people using the "clever" short notation. And there were people writing write-only, unreadable APL programs which performed complex calculations in just two or three lines of codes.
February 16, 2009 at 3:34 am
I agree with the previous posters about avoiding "*=".
However, doesn't "*=" equate to RIGHT OUTER JOIN ? thus the two sql pieces of code are not the same.
I would suggest this is why the counts are different rather than any Null records.
February 16, 2009 at 4:32 am
mike.woodward (2/16/2009)
I agree with the previous posters about avoiding "*=".
However, doesn't "*=" equate to RIGHT OUTER JOIN ? thus the two sql pieces of code are not the same.
I would suggest this is why the counts are different rather than any Null records.
Mike,
"*=" is a LEFT OUTER JOIN, the asterisk denoting which side to use the OUTER JOIN on
Kev
February 16, 2009 at 10:04 am
I don’t think that it has to do with the NULL values. When you work with from clause and you use an outer join, the server will use the criteria in the from clause only for the join, but it won’t use it for filtering the data, so when you specify in the from clause item_receipts.bonded='b', this criteria will be checked and the value of this column will effect only the values that will come from the inner table (item). This means that all the records from item_receipts will be returned to the client, but the records that have different value then ‘b’ in bonded column, will have null as the match in the columns from item table. Here is a small script that shows you this behavior:
use tempdb
go
create table a (i int, c char(1))
go
insert into a
select 1, 'a'
union select 2, 'b'
go
create table b (i int)
go
insert into b (i)
select 2 union select 3
go
--I don't specify any criteria about the value of column c
select a.i, a.c, b.i
from a left join b
on a.i = b.i
--I specify value that doesn't exist in the from clause.
--Since I specified it in the from clause and not in the where
--clause, the records from table a will return to the client
--but there will be no match from table b
select a.i, a.c, b.i
from a left join b
on a.i = b.i and a.c = 'w'
go
drop table a
drop table b
In your second query you specified the criteria in the where clause, so it did effect the number of records that will be returned from the outer table (item_receipts).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 16, 2009 at 11:29 am
I meant the effect of a NULL in a WHERE clause, not in the JOIN clause.
Something like
select a.i, a.c, b.i
from a left join b
on a.i = b.i and a.c = 'w'
WHERE A.some_column = @param
if your param is NULL or all the values of some_column are nulls, NO record will be selected and that is not an error.
February 16, 2009 at 12:46 pm
There is difference between join condition and where condition for outer joins. Briefly if join condition is not satisfied then rows from base table are kept and values for outer joined table are set to NULL. If where condition is not satisfied then rows are discarded. So most probably the additional predicate item_receipts.bonded='b' is the reason why result is different.
See more here Difference between predicates in ON clause vs. predicates in WHERE clause
Gints Plivna
http://www.gplivna.eu
February 16, 2009 at 1:48 pm
Just to clarify: Is the instance of SQL Server and database involved using a case-insensitive collation?
Ken
February 16, 2009 at 3:26 pm
See more here Difference between predicates in ON clause vs. predicates in WHERE clause
Nice !
By the way, the Original poster still has not provided the table script, data entry script and sql statement. I wonder if he is really interested in an answer...
February 16, 2009 at 3:37 pm
*= is a syntax that is obsolete and should not be used. It is not allowed in SQL Server 2005. *= and =* should be replaced with left or right out joins.
February 16, 2009 at 9:39 pm
J (2/16/2009)
By the way, the Original poster still has not provided the table script, data entry script and sql statement. I wonder if he is really interested in an answer...
He got several answers that explained him the difference between specifying the join condition in the from clause or in the where clause. Why do you think that he should post the question again with script and test data? Do you think that he has a different problem?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 17, 2009 at 2:32 am
Yes, the and item_receipts.bonded='b'
should be changed to where item_receipts.bonded='b' ,
Thank you all so much for replying this question, it offer me many valuable info.
February 18, 2009 at 8:06 am
Not to belabor an obscure and deprecated operation too much, but I'm surprised to learn in this post that the left or right side orientation of the '*' in the implicit join clause actually works backward to what I thought it did all these years. I thought the positioning of the '*' indicated the side that you wanted to get ALL the rows from regardless if there was a match from the table on the other side.
So if I wanted to get ALL the rows from table A in the example below regardless if there is a matching row in table B, I would of course first use the JOIN clause below:
SELECT a.column1
FROM tableA a
LEFT OUTER JOIN tableB b ON b.column1 = a.Column1
And I always thought the equivalent using an implicit join would be:
SELECT a.column1
FROM tableA a, tableB b
WHERE b.column1 =* a.Column1
Which acts as a RIGHT OUTER JOIN which depending on specifics could end up being functionally equivalent but using the wrong thought process.
I've never used the deprecated form in new SQL, but have run across it many times in legacy code and undoubtedly will see it again. At least now I know how to read it the right way.
Cheers
maddog
February 18, 2009 at 8:31 am
LEFT JOIN and RIGHT JOIN are completely the same, if tables are switched.
Just the same for *= and =* in case the columns/variables/constants are switched for the old syntax.
Gints Plivna
http://www.gplivna.eu
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply