在JOIN和WHERE中过滤查询之间的区别? [英] Difference between filtering queries in JOIN and WHERE?
问题描述
在SQL中,我试图根据ID过滤结果,并想知道两者之间是否存在逻辑差异
In SQL I am trying to filter results based on an ID and wondering if there is any logical difference between
SELECT value
FROM table1
JOIN table2 ON table1.id = table2.id
WHERE table1.id = 1
和
SELECT value
FROM table1
JOIN table2 ON table1.id = table2.id AND table1.id = 1
在我看来,逻辑似乎是不同的,尽管您总是会得到相同的结果集,但是我想知道在任何条件下您将获得两个不同的结果集(还是它们总是返回完全相同的结果)两个结果集)
To me, it seems as if the logic is different though you will always get the same set of results but I wondered if there were any conditions under which you would get two different result sets (or would they always return the exact same two result sets)
推荐答案
答案是否不同,但是:
我将始终喜欢执行以下操作.
I will always prefer to do the following.
- 始终将加入条件保留在
ON
子句中 - 始终将过滤器的放在
where
子句中
- Always keep the Join Conditions in
ON
clause - Always put the filter's in
where
clause
这使查询更具可读性.
所以我将使用以下查询:
So I will use this query:
SELECT value
FROM table1
INNER JOIN table2
ON table1.id = table2.id
WHERE table1.id = 1
但是,当您使用OUTER JOIN'S
时,将过滤器保持在ON
条件和Where
条件中会有很大的不同.
However when you are using OUTER JOIN'S
there is a big difference in keeping the filter in the ON
condition and Where
condition.
逻辑查询处理
以下列表包含查询的一般形式,以及根据逻辑上处理不同子句的顺序分配的步骤号.
The following list contains a general form of a query, along with step numbers assigned according to the order in which the different clauses are logically processed.
(5) SELECT (5-2) DISTINCT (5-3) TOP(<top_specification>) (5-1) <select_list>
(1) FROM (1-J) <left_table> <join_type> JOIN <right_table> ON <on_predicate>
| (1-A) <left_table> <apply_type> APPLY <right_table_expression> AS <alias>
| (1-P) <left_table> PIVOT(<pivot_specification>) AS <alias>
| (1-U) <left_table> UNPIVOT(<unpivot_specification>) AS <alias>
(2) WHERE <where_predicate>
(3) GROUP BY <group_by_specification>
(4) HAVING <having_predicate>
(6) ORDER BY <order_by_list>;
流程图逻辑查询处理
-
(1)FROM:FROM阶段标识查询的源表, 进程表运算符.每个表运算符都应用一系列 子阶段.例如,联接中涉及的阶段是(1-J1) 笛卡尔乘积,(1-J2)ON过滤器,(1-J3)添加外部行.来自 阶段生成虚拟表VT1.
(1) FROM: The FROM phase identifies the query’s source tables and processes table operators. Each table operator applies a series of sub phases. For example, the phases involved in a join are (1-J1) Cartesian product, (1-J2) ON Filter, (1-J3) Add Outer Rows. The FROM phase generates virtual table VT1.
(1-J1)笛卡尔积:此阶段执行笛卡尔积 (交叉连接)表运算符中涉及的两个表之间, 生成VT1-J1.
(1-J1) Cartesian Product: This phase performs a Cartesian product (cross join) between the two tables involved in the table operator, generating VT1-J1.
它是从 此出色链接 .
It is referred from this excellent link.
这篇关于在JOIN和WHERE中过滤查询之间的区别?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!