如何替换SQL中的左联接 [英] How can I substitute a left join in 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.

推荐答案

请记住，SQL的外部联接是一种关系联合，专门用于投影null值.如果要避免使用null值(我认为这是一件好事)，则应避免使用外部联接.请注意，现代关系语言完全放弃了null和外部联接的概念.

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.

此外部联接:

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

…在语义上等效于此SQL代码:

…is semantically equivalent to this SQL code:

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
                  );

第二个查询可能看起来很耗时，但这仅是由于SQL的设计/演化方式.以上只是自然连接，并集和半连接.但是，SQL没有半联接运算符，如果您的产品未实现标准SQL的NATURAL JOIN语法，则要求您在SELECT子句中指定列列表并编写JOIN子句，这导致需要表达大量代码很简单.

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.

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

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

这篇关于如何替换SQL中的左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！