INNER JOIN ON与WHERE子句 [英] INNER JOIN ON vs WHERE clause
问题描述
为简单起见,假设所有相关字段均为NOT NULL
.
For simplicity, assume all relevant fields are NOT NULL
.
您可以这样做:
SELECT
table1.this, table2.that, table2.somethingelse
FROM
table1, table2
WHERE
table1.foreignkey = table2.primarykey
AND (some other conditions)
否则:
SELECT
table1.this, table2.that, table2.somethingelse
FROM
table1 INNER JOIN table2
ON table1.foreignkey = table2.primarykey
WHERE
(some other conditions)
这两个在MySQL
中是否以相同的方式工作?
Do these two work on the same way in MySQL
?
推荐答案
INNER JOIN
是您应该使用的ANSI语法.
INNER JOIN
is ANSI syntax which you should use.
通常认为它更具可读性,尤其是当您连接很多表时.
It is generally considered more readable, especially when you join lots of tables.
只要有需要,也可以轻松地将其替换为OUTER JOIN
.
It can also be easily replaced with an OUTER JOIN
whenever a need arises.
WHERE
语法更面向关系模型.
The WHERE
syntax is more relational model oriented.
两个表JOIN
ed的结果是表的笛卡尔积,将对其应用过滤器,该过滤器仅选择那些连接列匹配的行.
A result of two tables JOIN
ed is a cartesian product of the tables to which a filter is applied which selects only those rows with joining columns matching.
使用WHERE
语法更容易看到这一点.
It's easier to see this with the WHERE
syntax.
以您的示例为例,在MySQL(通常在SQL中)这两个查询是同义词.
As for your example, in MySQL (and in SQL generally) these two queries are synonyms.
还要注意,MySQL还有一个STRAIGHT_JOIN
子句.
Also note that MySQL also has a STRAIGHT_JOIN
clause.
使用此子句,您可以控制JOIN
的顺序:在外部循环中扫描哪个表,在内部循环中扫描哪个表.
Using this clause, you can control the JOIN
order: which table is scanned in the outer loop and which one is in the inner loop.
您无法使用WHERE
语法在MySQL中进行控制.
You cannot control this in MySQL using WHERE
syntax.
这篇关于INNER JOIN ON与WHERE子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!