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 JOINed 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屋！