在 from 子句或 where 子句中进行 equi join 更好吗 [英] Is it better to do an equi join in the from clause or where clause

问题描述

在主键列上连接两个简单表并放置附加相等条件时，这可以在连接本身或 where 子句中完成.

When joining two simple tables on primary key columns and placing an addition equality condition this can be done in the join itself or the where clause.

例如以下是等价的.我的问题是 - 有没有理由使用一种样式而不是另一种样式?

SELECT * 
FROM A
INNER JOIN B ON A.A_ID = B.A_ID
            AND A.DURATION = 3.00

...对比:

SELECT * 
FROM A
INNER JOIN B ON A.A_ID = B.A_ID
WHERE A.DURATION = 3.00

推荐答案

这是一个风格问题.通常，您希望将定义结果集形状"的条件放在 FROM 子句中(即那些控制每个表中哪些行应该连接在一起以产生结果的条件)，而那些filter 结果集应该在 WHERE 子句中.对于INNER JOIN，效果是一样的，但是一旦涉及到OUTER JOIN(LEFT，RIGHT)，感觉就清晰了很多.

It's a style matter. Generally, you'd want to put the conditions that define the "shape" of the result set in the FROM clause (i.e. those that control which rows from each table should join together to produce a result), whereas those conditions which filter the result set should be in the WHERE clause. For INNER JOINs, the effects are equal, but once OUTER JOINs (LEFT, RIGHT) are involved, it feels a lot clearer.

在你的第一个例子中，我问这与表 B 有什么关系?"当我在 JOIN 中遇到这种奇怪的情况时.而在第二个中，如果我不感兴趣，我可以跳过 FROM 子句(和所有 JOIN)，只查看确定 WHERE 子句中是否要返回行的条件.

In your first example, I'm left asking "what has this got to do with Table B?" when I encounter this odd condition in the JOIN. Whereas in the second, I can skip over the FROM clause (and all JOINs) if I'm not interested, and just see the conditions which determine whether rows are going to be returned in the WHERE clause.

这篇关于在 from 子句或 where 子句中进行 equi join 更好吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！