通常，一旦两个表已经连接，就在WHERE子句中处理筛选。这是有可能的，不过您可能希望在连接它们之前过滤一个或两个表。 也就是说，where子句适用于整个结果集，而on子句只适用于相关的连接。

我的做法是:

Always put the join conditions in the ON clause if you are doing an INNER JOIN. So, do not add any WHERE conditions to the ON clause, put them in the WHERE clause. If you are doing a LEFT JOIN, add any WHERE conditions to the ON clause for the table in the right side of the join. This is a must, because adding a WHERE clause that references the right side of the join will convert the join to an INNER JOIN. The exception is when you are looking for the records that are not in a particular table. You would add the reference to a unique identifier (that is not ever NULL) in the RIGHT JOIN table to the WHERE clause this way: WHERE t2.idfield IS NULL. So, the only time you should reference a table on the right side of the join is to find those records which are not in the table.

关于你的问题，

只要你的服务器能得到它，内部连接的'on'和'where'都是一样的:

select * from a inner join b on a.c = b.c

and

select * from a inner join b where a.c = b.c

并非所有口译员都知道“where”选项，所以可能应该避免使用。当然，“on”从句更清楚。

当涉及到左连接时，where子句和on子句之间有很大的区别。

这里有一个例子:

mysql> desc t1; 
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| fid   | int(11)     | NO   |     | NULL    |       |
| v     | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

fid是表t2的id。

mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| v     | varchar(10) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

查询“on子句”:

mysql> SELECT * FROM `t1` left join t2 on fid = t2.id AND t1.v = 'K' 
    -> ;
+----+-----+---+------+------+
| id | fid | v | id   | v    |
+----+-----+---+------+------+
|  1 |   1 | H | NULL | NULL |
|  2 |   1 | B | NULL | NULL |
|  3 |   2 | H | NULL | NULL |
|  4 |   7 | K | NULL | NULL |
|  5 |   5 | L | NULL | NULL |
+----+-----+---+------+------+
5 rows in set (0.00 sec)

where子句查询:

mysql> SELECT * FROM `t1` left join t2 on fid = t2.id where t1.v = 'K';
+----+-----+---+------+------+
| id | fid | v | id   | v    |
+----+-----+---+------+------+
|  4 |   7 | K | NULL | NULL |
+----+-----+---+------+------+
1 row in set (0.00 sec)

很明显， 第一个查询从t1返回一条记录，从t2返回与t1相关的行(如果有的话)。v = 'K'。

第二个查询返回来自t1的行，但只针对t1。v = 'K'将有任何与它相关的行。

内连接不重要吗 外部连接的注意事项 a. WHERE从句:加入后。在连接发生后，将过滤记录。 b. ON条款-加入前。记录(来自右表)将在加入之前被过滤。这可能在结果中以null结束(因为OUTER连接)。

示例:考虑以下表格:

文档: id 的名字 1 Document1 2 Document2 3. Document3 4 Document4 5 Document5 下载: id document_id 用户名 1 1 sandeep 2 1 思米 3. 2 sandeep 4 2 •拉赫曼 5 3. 思米

a) WHERE从句内:

   SELECT documents.name, downloads.id
     FROM documents
     LEFT OUTER JOIN downloads
       ON documents.id = downloads.document_id
     WHERE username = 'sandeep'

对于上述查询，中间连接表将如下所示。

在应用WHERE子句并选择列出的属性后，结果将是:

b)在JOIN子句内

   SELECT documents.name, downloads.id
   FROM documents
     LEFT OUTER JOIN downloads
       ON documents.id = downloads.document_id
         AND username = 'sandeep'

对于上述查询，中间连接表将如下所示。

注意文档中不符合这两个条件的行是如何用NULL值填充的。

选择列出的属性后，结果将是:

让我们考虑一下这些表格:

A

id | SomeData

B

id | id_A | SomeOtherData

id_A是表a的外键

写这个查询:

SELECT *
FROM A
LEFT JOIN B
ON A.id = B.id_A;

将提供如下结果:

/ : part of the result
                                       B
                      +---------------------------------+
            A         |                                 |
+---------------------+-------+                         |
|/////////////////////|///////|                         |
|/////////////////////|///////|                         |
|/////////////////////|///////|                         |
|/////////////////////|///////|                         |
|/////////////////////+-------+-------------------------+
|/////////////////////////////|
+-----------------------------+

A中有而B中没有的东西意味着B中有空值。

现在，让我们考虑B.id_A中的一个特定部分，并从前面的结果中突出显示它:

/ : part of the result
* : part of the result with the specific B.id_A
                                       B
                      +---------------------------------+
            A         |                                 |
+---------------------+-------+                         |
|/////////////////////|///////|                         |
|/////////////////////|///////|                         |
|/////////////////////+---+///|                         |
|/////////////////////|***|///|                         |
|/////////////////////+---+---+-------------------------+
|/////////////////////////////|
+-----------------------------+

写这个查询:

SELECT *
FROM A
LEFT JOIN B
ON A.id = B.id_A
AND B.id_A = SpecificPart;

将提供如下结果:

/ : part of the result
* : part of the result with the specific B.id_A
                                       B
                      +---------------------------------+
            A         |                                 |
+---------------------+-------+                         |
|/////////////////////|       |                         |
|/////////////////////|       |                         |
|/////////////////////+---+   |                         |
|/////////////////////|***|   |                         |
|/////////////////////+---+---+-------------------------+
|/////////////////////////////|
+-----------------------------+

因为这将在内部连接中删除不在B.id_A = SpecificPart中的值

现在，让我们把查询改为这样:

SELECT *
FROM A
LEFT JOIN B
ON A.id = B.id_A
WHERE B.id_A = SpecificPart;

结果是:

/ : part of the result
* : part of the result with the specific B.id_A
                                       B
                      +---------------------------------+
            A         |                                 |
+---------------------+-------+                         |
|                     |       |                         |
|                     |       |                         |
|                     +---+   |                         |
|                     |***|   |                         |
|                     +---+---+-------------------------+
|                             |
+-----------------------------+

因为整个结果是根据B. id_a = SpecificPart过滤的，删除了B. id_a为NULL的部分，这些部分在A中，但不在B中