Sybase 外连接行为

发布于 2024-11-03 08:01:24 字数 790 浏览 5 评论 0原文

我正在研究一些旧版 SQL（用 sybase 术语编写）并将其转换为 SQL-92 兼容形式。问题是，我无法理解“*=”的行为方式。

查询（适当审查）：

SELECT 
    C.*, SUM(P.qty) from Cars C, Parts P
WHERE 
    C.carid = 1
AND 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

我将其转换为：

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

但事实证明 - 它们并不等效。有人可以帮助我理解 sybase 外连接中“*=”的确切行为是什么以及为什么我的两个查询不相同？

谢谢:)

PS：我正在使用 Sybase ASE TDS 5.5

原文

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
AND 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

分享到QQ

分享到微博