开发者

Sybase outer join behaviour

开发者 https://www.devze.com 2023-03-01 13:23 出处:网络
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.

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消