INNER JOIN 与“FROM”中的多个表名比较
之间有什么区别INNER JOIN 查询和隐式联接 查询（即在FROM 关键字后列出多个表）？
例如，给定以下两个表：
CREATE TABLE Statuses(
id INT PRIMARY KEY,
description VARCHAR(50)
);
INSERT INTO Statuses VALUES (1, 'status');
CREATE TABLE Documents(
id INT PRIMARY KEY,
statusId INT REFERENCES Statuses(id)
);
INSERT INTO Documents VALUES (9, 1);
以下两个 SQL 查询有什么区别？
根据我所做的测试，他们返回相同的结果。他们做同样的事情吗？在某些情况下它们会返回不同的结果集吗？
-- Using implicit join (listing multiple tables)
SELECT s.description
FROM Documents d, Statuses s
WHERE d.statusId = s.id
AND d.id = 9;
-- Using INNER JOIN
SELECT s.description
FROM Documents d
INNER JOIN Statuses s ON d.statusId = s.id
WHERE d.id = 9;
Possible Duplicate:
INNER JOIN versus WHERE clause — any difference?
What is the difference between an INNER JOIN query and an implicit join query (i.e. listing multiple tables after the FROM keyword)?
For example, given the following two tables:
CREATE TABLE Statuses(
id INT PRIMARY KEY,
description VARCHAR(50)
);
INSERT INTO Statuses VALUES (1, 'status');
CREATE TABLE Documents(
id INT PRIMARY KEY,
statusId INT REFERENCES Statuses(id)
);
INSERT INTO Documents VALUES (9, 1);
What is the difference between the below two SQL queries?
From the testing I've done, they return the same result. Do they do the same thing? Are there situations where they will return different result sets?
-- Using implicit join (listing multiple tables)
SELECT s.description
FROM Documents d, Statuses s
WHERE d.statusId = s.id
AND d.id = 9;
-- Using INNER JOIN
SELECT s.description
FROM Documents d
INNER JOIN Statuses s ON d.statusId = s.id
WHERE d.id = 9;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
没有理由使用隐式连接（带逗号的连接）。是的，对于内部联接，它将返回相同的结果。然而，它会受到无意的交叉联接的影响，尤其是在复杂的查询中，并且维护起来更加困难，因为左/右外联接语法（在 SQL Server 中已弃用，现在无论如何都不能正常工作）因供应商而异。由于您不应该在同一查询中混合隐式连接和显式连接（您可能会得到错误的结果），因此需要将某些内容更改为左连接意味着重写整个查询。
There is no reason to ever use an implicit join (the one with the commas). Yes for inner joins it will return the same results. However, it is subject to inadvertent cross joins especially in complex queries and it is harder for maintenance because the left/right outer join syntax (deprecated in SQL Server, where it doesn't work correctly right now anyway) differs from vendor to vendor. Since you shouldn't mix implicit and explict joins in the same query (you can get wrong results), needing to change something to a left join means rewriting the entire query.
如果你用第一种方法，30岁以下的人可能会嘲笑你，但只要你进行内连接，他们就会产生相同的结果，优化器将生成相同的执行计划（至少与据我所知）。
当然，这假定第一个查询中的 where 子句就是您在第二个查询中加入的方式。
顺便说一句，这可能会作为重复项被关闭。
If you do it the first way, people under the age of 30 will probably chuckle at you, but as long as you're doing an inner join, they produce the same result and the optimizer will generate the same execution plan (at least as far as I've ever been able to tell).
This does of course presume that the where clause in the first query is how you would be joining in the second query.
This will probably get closed as a duplicate, btw.
第二种方法的优点在于它有助于将连接条件（on ...）与过滤条件（where ...）分开。这有助于使查询的意图更具可读性。
连接条件通常更能描述数据库的结构和表之间的关系。例如，工资表通过 EmployeeID 列与雇员表相关，并且涉及这两个表的查询可能总是在该列上联接。
过滤条件更能描述查询正在执行的特定任务。如果查询是 FindRichPeople，则 where 子句可能是“where salaries.Salary > 1000000”...这描述了手头的任务，而不是数据库结构。
请注意，SQL 编译器不会这样看...如果它认为交叉连接然后过滤结果会更快，它将交叉连接并过滤结果。它不关心 ON 子句中的内容和 WHERE 子句中的内容。但是，如果 on 子句与外键匹配或连接到主键或索引列，则通常不会发生这种情况。就正确操作而言，它们是相同的；就编写可读、可维护的代码而言，第二种方法可能更好一些。
The nice part of the second method is that it helps separates the join condition (on ...) from the filter condition (where ...). This can help make the intent of the query more readable.
The join condition will typically be more descriptive of the structure of the database and the relation between the tables. e.g., the salary table is related to the employee table by the EmployeeID column, and queries involving those two tables will probably always join on that column.
The filter condition is more descriptive of the specific task being performed by the query. If the query is FindRichPeople, the where clause might be "where salaries.Salary > 1000000"... thats describing the task at hand, not the database structure.
Note that the SQL compiler doesn't see it that way... if it decides that it will be faster to cross join and then filter the results, it will cross join and filter the results. It doesn't care what is in the ON clause and whats in the WHERE clause. But, that typically wont happen if the on clause matches a foreign key or joins to a primary key or indexed column. As far as operating correctly, they are identical; as far as writing readable, maintainable code, the second way is probably a little better.
据我所知，没有什么区别，第二个是使用内部联接编写此类语句的新方法，而第一个是旧方法。
there is no difference as far as I know is the second one with the inner join the new way to write such statements and the first one the old method.
第一个对这两个表中的所有记录进行笛卡尔积，然后通过 where 子句进行过滤。
第二个仅连接满足 ON 子句要求的记录。
编辑：正如其他人所指出的，优化引擎将处理笛卡尔积的尝试，并将或多或少地产生相同的查询。
The first one does a Cartesian product on all record within those two tables then filters by the where clause.
The second only joins on records that meet the requirements of your ON clause.
EDIT: As others have indicated, the optimization engine will take care of an attempt on a Cartesian product and will result in the same query more or less.
有点一样。可以帮助你。
A bit same. Can help you out.
在您给出的示例中，查询是等效的；如果您使用的是 SQL Server，请运行查询并显示实际的执行计划以查看服务器内部正在执行的操作。
In the example you've given, the queries are equivalent; if you're using SQL Server, run the query and display the actual exection plan to see what the server's doing internally.