I'm working on some legacy SQL (written in sybase lingo) and converting it to SQL-92 compatible form. The problem is, I'm unable to understand how "*=" behaves.
the query (appropriately censored):
SELECT
C.*, SUM(P.qty) from Cars C, Parts P
WHERE
C.carid = 1
AN开发者_如何学PythonD C.carid *= P.carid
AND C.missingpart *= P.missingpart
AND C.manufacturer *= P.manufacturer
AND P.qty is not null
GROUP BY C.carid, C.missingpart, C.manufacturer
I converted it to:
SELECT
C.*, SUM(P.qty) from Cars C
LEFT JOIN Parts P
ON C.carid = P.carid
AND C.missingpart = P.missingpart
AND C.manufacturer = P.manufacturer
WHERE
C.carid = 1
AND P.qty is not null
GROUP BY C.carid, C.missingpart, C.manufacturer
But turns out - they are not equivalent. Can someone help me understand what is the exact behaviour of "*=" in sybase outer joins and why my two queries are not identical?
Thanks :)
PS: I'm using Sybase ASE TDS 5.5
Try moving AND P.qty is not null
from the WHERE clause to the join expression.
精彩评论