sql - INNER JOIN ON与WHERE子句(INNER JOIN ON vs WHERE clause)

Question

For simplicity, assume all relevant fields are NOT NULL .

(为简单起见，假设所有相关字段都不为NOT NULL 。)

You can do:

(你可以做：)

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1, table2
WHERE
    table1.foreignkey = table2.primarykey
    AND (some other conditions)

Or else:

(要不然：)

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1 INNER JOIN table2
    ON table1.foreignkey = table2.primarykey
WHERE
    (some other conditions)

Do these two work on the same way in MySQL ?

(这两个在MySQL是否以相同的方式工作？)

  ask by JCCyC translate from so

Answer 1

INNER JOIN is ANSI syntax which you should use.

(INNER JOIN是您应该使用的ANSI语法。)

It is generally considered more readable, especially when you join lots of tables.

(通常认为它更具可读性，尤其是当您连接许多表时。)

It can also be easily replaced with an OUTER JOIN whenever a need arises.

(如有需要，也可以轻松地将其替换为OUTER JOIN 。)

The WHERE syntax is more relational model oriented.

(WHERE语法更面向关系模型。)

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.

(两个表JOIN ed的结果是表的笛卡尔积，将对其应用过滤器，该过滤器仅选择连接列匹配的那些行。)

It's easier to see this with the WHERE syntax.

(使用WHERE语法更容易看到这一点。)

As for your example, in MySQL (and in SQL generally) these two queries are synonyms.

(以您的示例为例，在MySQL（通常在SQL中）中，这两个查询是同义词。)

Also note that MySQL also has a STRAIGHT_JOIN clause.

(另请注意，MySQL还具有STRAIGHT_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.

(使用此子句，您可以控制JOIN顺序：在外部循环中扫描哪个表，在内部循环中扫描哪个表。)

You cannot control this in MySQL using WHERE syntax.

(您无法使用WHERE语法在MySQL中控制此功能。)