“where”和“join”在mysql中有什么区别?(复制)

Possible Duplicate:
In MySQL queries, why use join instead of where?

可能重复:在MySQL查询中，为什么要使用join而不是where?

Joins are always confusing for me..can anyone tell me what is difference between different joins and which one is quickest and when to use which join and what is difference between join and where clause ?? Plz give ur answer in detail as i already read about joins on some websites but didn't get the concept properly.

加入总是让我感到困惑。谁能告诉我不同的连接和哪个是最快的，什么时候使用哪个连接，连接和where子句之间的区别是什么?Plz给了你详细的答案，因为我已经在一些网站上读到过关于连接的内容，但是并没有很好地理解这个概念。

2 个解决方案

#1

Rather than quote an entire Wikipedia article, I'm going to suggest your their article on SQL Joins.

我不会引用*上的一篇文章，而是建议您阅读关于SQL连接的文章。

An SQL JOIN clause combines records from two or more tables in a database. It creates a set that can be saved as a table or used as is. A JOIN is a means for combining fields from two tables by using values common to each. ANSI standard SQL specifies four types of JOINs: INNER, OUTER, LEFT, and RIGHT. In special cases, a table (base table, view, or joined table) can JOIN to itself in a self-join.

SQL JOIN子句组合来自数据库中的两个或多个表的记录。它创建一个集，可以保存为表或按原样使用。连接是一种通过使用每个表共有的值来组合两个表中的字段的方法。ANSI标准SQL指定了四种类型的连接:内连接、外连接、左连接和右连接。在特殊情况下，一个表(基表、视图或连接表)可以加入到自连接中。

A programmer writes a JOIN predicate to identify the records for joining. If the evaluated predicate is true, the combined record is then produced in the expected format, a record set or a temporary table, for example.

程序员编写连接谓词来识别连接的记录。如果计算的谓词为true，那么合并的记录将以预期的格式生成，例如记录集或临时表。

There is an older syntax that uses WHERE clauses to imply INNER JOINs. While it works, and will produce queries that run exactly like queries specified with the INNER JOIN syntax, it is deprecated because most people find it more confusing.

有一种较老的语法使用WHERE子句来表示内部连接。虽然它可以工作，并且将生成与内部连接语法指定的查询完全相同的查询，但是它被弃用了，因为大多数人认为它更令人困惑。

Here's the documentation for the MySQL JOIN syntax.

下面是MySQL连接语法的文档。

#2

This query:

这个查询:

SELECT c.customer_name, o.order_id, o.total
FROM customers c, orders o
WHERE c.id = o.customer_id

and this

这

SELECT c.customer_name, o.order_id, o.total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id

make no difference on what is executed on the mysql server but the join is (imho) way more readable. Expecially for more complex joins:

在mysql服务器上执行什么并不重要，但是连接(imho)更容易读。特别是对于更复杂的连接:

Here is an article about the topic: http://www.mysqlperformanceblog.com/2010/04/14/is-there-a-performance-difference-between-join-and-where/

这里有一篇关于这个主题的文章:http://www.mysqlperformanceblog.com/2010/04/14/is-there-a-performance-difference between- joinand -where/

#1