SQL Sybase ASE 15 中左连接和 *= 之间的区别

Difference between left join and *= in Sybase ASE 15

提问by R Vive L OL

I need help to understand this matter, what's the difference between the 2 queries below, all I know that they don't return the same result.

我需要帮助来理解这件事，下面的 2 个查询有什么区别，我只知道它们不返回相同的结果。

Query 1:

查询 1：

SELECT a.col1, b.c1
  FROM A a
  LEFT JOIN B b
    ON a.col1 = b.c1
 WHERE b.status = 'Y'

Query 2:

查询 2：

SELECT a.col1, b.c1
  FROM A a, B b
 WHERE a.col1 *= b.c1
   AND b.status = 'Y'

回答by ypercube??

The first query:

第一个查询：

SELECT
       a.col1, b.c1 
FROM 
       a LEFT JOIN b ON a.col1 = b.c1 
WHERE
       b.status = 'Y' ;

is equivalent to an inner join because the b.statuscolumn (from the right side of a left outer join) is used in the WHEREpart:

等价于内连接，因为b.status列（来自左外连接的右侧）用于WHERE部件中：

SELECT
       a.col1, b.c1 
FROM 
       a INNER JOIN b ON a.col1 = b.c1 
WHERE
       b.status = 'Y' ;

---

---

The 2nd query is (probably) executed as:

第二个查询（可能）执行为：

SELECT
       a.col1, b.c1 
FROM 
       a LEFT JOIN b ON a.col1 = b.c1 
                    AND b.status = 'Y' ;

which may give different results as it is a (logically) different query.

这可能会给出不同的结果，因为它是一个（逻辑上）不同的查询。

That's one of the reasons you should never use this old syntax. It is ambiguous sometimes, e.g. when there are more than one conditions or more than one outer joins.

这就是您永远不应该使用这种旧语法的原因之一。有时它是模棱两可的，例如当有多个条件或多个外部联接时。

回答by HLGEM

I know Sybase and SQL Server are closely related. The *=has been removed from SQL Server but even as far back as SQL Server 2000, it was not working correctly, sometimes interpreting as a left join and sometimes as cross join. Since Sybase and SQL Server came from the same base product, I would suspect this is also your problem with it and why the results are different. Do not use the implicit join for an outer join as it will not reliably give the correct answer.

我知道 Sybase 和 SQL Server 密切相关。在*=已经从SQL Server中删除，但甚至早在SQL Server 2000中，它无法正常工作，有时解释为左连接，有时甚至交叉连接。由于 Sybase 和 SQL Server 来自同一个基础产品，我怀疑这也是您的问题以及结果不同的原因。不要对外部连接使用隐式连接，因为它不能可靠地给出正确答案。

Here is a direct quote from Books Online for SQL Server 2000 that discusses this issue:

以下是讨论此问题的 SQL Server 2000 联机丛书中的直接引述：

In earlier versions of Microsoft? SQL Server? 2000, left and right outer join conditions were specified in the WHEREclause using the *=and =*operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way. SQL-92 compliant outer joins are specified in the FROMclause and do not result in this ambiguity.

在早期版本的 Microsoft 中？SQL服务器？2000 年，WHERE使用*=和=*运算符在子句中指定了左右外连接条件。在某些情况下，此语法会导致可以以多种方式解释的模糊查询。符合 SQL-92 的外连接在FROM子句中指定，不会导致这种歧义。

回答by R Vive L OL

Sorry to be a little bit late, but i found the solution: In the old syntax *=, the condition b.Status = 'Y'will be in the left join on clause, so to have to same result in the first query I just moved the b.Status = 'Y'to the "on" clause.

抱歉有点晚了，但我找到了解决方案：在旧语法中*=，条件b.Status = 'Y'将在 left join on 子句中，因此要在第一个查询中获得相同的结果，我只是将其b.Status = 'Y'移至“on”子句.

回答by Laurence

These queries look the same. You say they don't return the same results. Do you have an example?

这些查询看起来相同。你说他们不会返回相同的结果。你有例子吗？

Where the old and new join notations do differ is if you moved the where to the join

新旧连接符号的不同之处在于，如果您将位置移动到连接

Select
  a.col1, 
  b.c1
From
  A
    Left Join
  B 
    On a.col1 = b.c1 And b.Status = 'Y';

This is different, and can't be so easily represented in the old notation (at least not in Oracle's, don't have direct experience of Sybase)

这是不同的，不能那么容易地用旧符号表示（至少在 Oracle 中没有，没有 Sybase 的直接经验）

Example(albeit Oracle uses (+)instead of *=)

示例（尽管 Oracle 使用(+)代替*=）

回答by Prakriti

I tried to find conversion of old queries to new queries when more than 2 tables with many where clauses are involved, but could not find it anywhere online, thus posting my solution here. The result will exactly match if you follow the trick below:

当涉及多个 where 子句的表超过 2 个时，我试图找到旧查询到新查询的转换，但无法在网上任何地方找到它，因此在此处发布我的解决方案。如果您遵循以下技巧，结果将完全匹配：

In case of below query:

在以下查询的情况下：

    select a.*
    from A a, B b, C c
    where a.server_id=0
    and a.name = 'John'
    and a.country_id*=b.value_cd
    and b.table_nm = 'Employee'
    and b.Attribute_nm ='A_nm'
    and a.state_id*= c.value_cd
    and c.table_nm = 'Company'

All where conditions except for first table (i.e. Table A) can go into on clause in join, however Where condition with first table should remain in Where clause as in the below query:

除了第一个表（即表 A）之外的所有 where 条件都可以进入 join 中的 on 子句，但是第一个表的 Where 条件应保留在 Where 子句中，如下面的查询所示：

    select a.*
    from A a
    left join B b on a.country_id = b.value_cd 
       and b.table_nm = 'Employee' 
       and b.Attribute_nm ='A_nm'
    left join C c on a.state_id = c.value_cd 
       and c.table_nm = 'Company' 
    where a.server_id=0
       and a.name = 'John'