“FROM a LEFT JOIN b"的区别与“来自 a，b" [英] Difference of "FROM a LEFT JOIN b" vs. "FROM a, b"

问题描述

查询是否相同?标准是什么?

Do the queries do the same? What is the standard?

如果更改其中一种编写查询的方式，我会失去性能吗?

Will I lose performance if I change one of these ways of write the query?

查询 1

   SELECT a.*, b.id AS b_id
     FROM table_a AS a
LEFT JOIN table_b AS b
       ON a.id = b.id

查询 2

   SELECT a.*, b.id AS b_id
     FROM table_a a, table_b b
    WHERE a.id = b.id

推荐答案

它们返回不同的结果.

即使 table_b 中没有与 table_a id 匹配的关联记录，LEFT JOIN 语句也会返回行.因此它将返回 table_a 中的所有行，与 table_a 中的匹配行或空/空 table_b 行配对(如果 table_a 中的该行在 table_b 中没有任何匹配行).

A LEFT JOIN statement will return rows even if there is no associated records in table_b that match table_a id. So it will return all rows in table_a, paired with EITHER a matching row in table_a OR a blank/null table_b row (if for that row in table_a there isn't any matching row in table_b).

第二个查询是 INNER JOIN 的快捷方式.此查询将仅返回与条件 a.id = b.id 匹配的行.第二个查询也可以写成:

The second query is a shortcut for an INNER JOIN. This query will ONLY exclusively return rows that match the condition a.id = b.id. The second query can also be written as:

SELECT a.*, b.id AS b_id
FROM table_a a
INNER JOIN table_b b 
ON a.id = b.id

要回答您的性能问题，请参阅相关 SO 线程上的答案 此处.

To answer your performance question, please see the answers on a related SO thread here.

这篇关于“FROM a LEFT JOIN b"的区别与“来自 a，b"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！