如何在SQL中替换左连接

Can anyone tell me how can I write the equivalent of a left join without really using left joins.

任何人都可以告诉我如何在不使用左连接的情况下编写等效的左连接。

Select * from a left join b on a.name = b.name.

3 个解决方案

#1

Bear in mind that SQL’s outer join is a kind of relational union which is expressly designed to project null values. If you want to avoid using the null value (a good thing, in my opinion), you should avoid using outer joins. Note that modern relational languages have dispensed with the concept of null and outer join entirely.

请记住，SQL的外连接是一种关系联合，它专门用于投影空值。如果你想避免使用null值（在我看来是一件好事），你应该避免使用外连接。请注意，现代关系语言完全没有使用null和outer连接的概念。

This outer join:

这个外连接：

SELECT DISTINCT T1.id, T1.value, T2.other_value
  FROM T1
       LEFT OUTER JOIN T2
          ON T1.id = T2.id;

…is semantically equivalent to this SQL code:

...在语义上等效于此SQL代码：

SELECT T1.id, T1.value, T2.other_value
  FROM T1
       INNER JOIN T2
          ON T1.id = T2.id
UNION
SELECT T1.id, T1.value, NULL
  FROM T1
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM T2
                    WHERE T1.id = T2.id
                  );

The second query may look long winded but that’s only because of the way SQL has been designed/evolved. The above is merely a natural join, a union and a semijoin. However, SQL has no semijoin operator, requires you to specify column lists in the SELECT clause and to write JOIN clauses if your product hasn’t implemented Standard SQL’s NATURAL JOIN syntax, which results in a lot of code to express something quite simple.

第二个查询可能看起来很长，但这只是因为SQL的设计/演变方式。以上只是一个自然的联合，一个联盟和一个半联盟。但是，SQL没有semijoin运算符，如果您的产品没有实现标准SQL的NATURAL JOIN语法，则要求您在SELECT子句中指定列列表并编写JOIN子句，这会导致很多代码表达非常简单的内容。

Therefore, you could write code such as the second query above but using an actual default value rather than the null value.

因此，您可以编写代码，例如上面的第二个查询，但使用实际的默认值而不是空值。

#2

Although left outer joins are the recommended way of writing sql as compared to an alternative but still we can achieve left outer join by using union for the resultset. If we have two table Table1 and Table 2 then we can achieve this by the following -

虽然左外连接是建议的编写sql的方法，但是我们仍然可以通过使用union来实现结果集的左外连接。如果我们有两个表Table1和表2，那么我们可以通过以下方式实现这一点 -

select
       A.Id, 
       A.Col1, 
       A.Col2, 
       A.Col3, 
       B.Id Col4, 
       B.Col1 Col5, 
       B.Col2 Col6, 
       B.Col3 Col7, 
       B.Col4 Col8
from   Table1 A, Table2 B
where  A.Id = B.Id

union all

select
       A.Id, 
       A.Col1, 
       A.Col2, 
       A.Col3, 
       null as Col4, 
       null as Col5, 
       null as Col6, 
       null as Col7, 
       null as Col8
from   Table1 A
where  not exists(select 1 from Table2 B where B.Id = A.Id)

This will help achieve the same result but because of the use of Union there could be some performance problems in case the tables are large.

这将有助于实现相同的结果，但由于使用Union，如果表很大，可能会出现一些性能问题。

#3

Oracle disguised left join:

甲骨文伪装左连接：

SELECT * FROM A, B
 WHERE a.name = b.name(+)

Generic :

通用：

SELECT a.*, case c.existence WHEN 1 then c.name ELSE NULL END
  FROM A
 INNER JOIN ( SELECT name name, 1 existence FROM B
               UNION ALL
              SELECT a.name name, 0 existence FROM A
               WHERE a.name NOT IN ( SELECT name FROM B )
            ) C
    ON c.name = a.name

#1