数据库结构CREATE TABLE `t1`(
`id` INT(1) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(10) NOT NULL
)ENGINE=InnoDB;CREATE TABLE `t2`(
`tid` INT(1) NOT NULL,
`qty` INT(1) NOT NULL,
`date` DATE NOT NULL,
KEY `tid`(`tid`),
CONSTRAINT `t2_ibkf_1` FOREIGN KEY (`tid`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB;测试数据INSERT INTO `t1` VALUES
(1, '张三'),
(2, '关二'),
(3, '刘大');INSERT INTO `t2` VALUES
(1, 10, '2011-03-05'),
(2, 10, '2011-03-05'),
(3, 10, '2011-03-05'),
(1, 10, '2011-04-10'),
(1, 10, '2011-04-10'),
(2, 10, '2011-04-10'),
(2, 10, '2011-04-10'),
(2, 10, '2011-04-10');期望结果+------+------+
| name | qty |
+------+------+
| 张三 | 20 |
| 关二 | 30 |
| 刘大 | NULL |
+------+------+问题/* 为什么这样得出的结果没有NULL行呢?*/
SELECT `name`,SUM(`qty`)
FROM `t1` LEFT JOIN `t2` ON `id`=`tid`
WHERE `date`>='2011-04-01'
GROUP BY `id`;
/* 这样就行 */
SELECT `name`,`qty`
FROM `t1` LEFT JOIN (
SELECT `tid`,SUM(`qty`) AS `qty`
FROM `t2`
WHERE `date`>='2011-04-01'
GROUP BY `tid`
) AS `t` ON `id`=`tid`;
LEFT JOIN查询中,WHERE语句的条件是作用于哪个表呢??
我google了下,看了这个人的BLOG
http://www.path8.net/tn/archives/967
其中有句话
如果照他这样说,也就是我第一个SQL语句中,WHERE `date`>='2011-04-01'对 t2表 不起作用??即使那样的话,那 t1表 的所有数据也还是应该返回不是吗??顺便一问,EXPLAIN时,有时“Extra”列是空的,什么也没有,这是什么意思呢??什么也没有和使用Index哪个好??
mysql> EXPLAIN
-> SELECT `name`,`qty`
-> FROM `t1` LEFT JOIN (
-> SELECT `tid`,SUM(`qty`) AS `qty`
-> FROM `t2`
-> WHERE `date`>='2011-04-01'
-> GROUP BY `tid`
-> ) AS `t` ON `id`=`tid`;
+----+-------------+------------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 3 | |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | |
| 2 | DERIVED | t2 | index | NULL | tid | 4 | NULL | 8 | Using where |
+----+-------------+------------+-------+---------------+------+---------+------+------+-------------+
`id` INT(1) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(10) NOT NULL
)ENGINE=InnoDB;CREATE TABLE `t2`(
`tid` INT(1) NOT NULL,
`qty` INT(1) NOT NULL,
`date` DATE NOT NULL,
KEY `tid`(`tid`),
CONSTRAINT `t2_ibkf_1` FOREIGN KEY (`tid`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB;测试数据INSERT INTO `t1` VALUES
(1, '张三'),
(2, '关二'),
(3, '刘大');INSERT INTO `t2` VALUES
(1, 10, '2011-03-05'),
(2, 10, '2011-03-05'),
(3, 10, '2011-03-05'),
(1, 10, '2011-04-10'),
(1, 10, '2011-04-10'),
(2, 10, '2011-04-10'),
(2, 10, '2011-04-10'),
(2, 10, '2011-04-10');期望结果+------+------+
| name | qty |
+------+------+
| 张三 | 20 |
| 关二 | 30 |
| 刘大 | NULL |
+------+------+问题/* 为什么这样得出的结果没有NULL行呢?*/
SELECT `name`,SUM(`qty`)
FROM `t1` LEFT JOIN `t2` ON `id`=`tid`
WHERE `date`>='2011-04-01'
GROUP BY `id`;
/* 这样就行 */
SELECT `name`,`qty`
FROM `t1` LEFT JOIN (
SELECT `tid`,SUM(`qty`) AS `qty`
FROM `t2`
WHERE `date`>='2011-04-01'
GROUP BY `tid`
) AS `t` ON `id`=`tid`;
LEFT JOIN查询中,WHERE语句的条件是作用于哪个表呢??
我google了下,看了这个人的BLOG
http://www.path8.net/tn/archives/967
其中有句话
如果照他这样说,也就是我第一个SQL语句中,WHERE `date`>='2011-04-01'对 t2表 不起作用??即使那样的话,那 t1表 的所有数据也还是应该返回不是吗??顺便一问,EXPLAIN时,有时“Extra”列是空的,什么也没有,这是什么意思呢??什么也没有和使用Index哪个好??
mysql> EXPLAIN
-> SELECT `name`,`qty`
-> FROM `t1` LEFT JOIN (
-> SELECT `tid`,SUM(`qty`) AS `qty`
-> FROM `t2`
-> WHERE `date`>='2011-04-01'
-> GROUP BY `tid`
-> ) AS `t` ON `id`=`tid`;
+----+-------------+------------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 3 | |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | |
| 2 | DERIVED | t2 | index | NULL | tid | 4 | NULL | 8 | Using where |
+----+-------------+------------+-------+---------------+------+---------+------+------+-------------+
你先执行一下
SELECT *
FROM `t1` LEFT JOIN `t2` ON `id`=`tid`
WHERE `date`>='2011-04-01'看看结果就明白了。
SELECT * FROM `t1` LEFT JOIN `t2` ON `id`=`tid` WHERE `date`>='2011-04-01'
是
“先查询出左边表的数据”,得到
+----+------+
| id | name |
+----+------+
| 1 | 张三 |
| 2 | 关二 |
| 3 | 刘大 |
+----+------+
“再连接右边表的数据”,得到
+----+------+-----+-----+------------+
| id | name | tid | qty | date |
+----+------+-----+-----+------------+
| 1 | 张三 | 1 | 10 | 2011-03-05 |
| 2 | 关二 | 2 | 10 | 2011-03-05 |
| 3 | 刘大 | 3 | 10 | 2011-03-05 |
| 1 | 张三 | 1 | 10 | 2011-04-10 |
| 1 | 张三 | 1 | 10 | 2011-04-10 |
| 2 | 关二 | 2 | 10 | 2011-04-10 |
| 2 | 关二 | 2 | 10 | 2011-04-10 |
| 2 | 关二 | 2 | 10 | 2011-04-10 |
+----+------+-----+-----+------------+
“之后再进行条件过滤”,得到
+----+------+-----+-----+------------+
| id | name | tid | qty | date |
+----+------+-----+-----+------------+
| 1 | 张三 | 1 | 10 | 2011-04-10 |
| 1 | 张三 | 1 | 10 | 2011-04-10 |
| 2 | 关二 | 2 | 10 | 2011-04-10 |
| 2 | 关二 | 2 | 10 | 2011-04-10 |
| 2 | 关二 | 2 | 10 | 2011-04-10 |
+----+------+-----+-----+------------+
如果是这样,那和默认的 INNER JOIN 的区别是什么呢??
是不是第2步,假设t2中完全没有“刘大”的数据,也就是,t2中没有
+-----+-----+------------+
| tid | qty | date |
+-----+-----+------------+
| 3 | 10 | 2011-03-05 |
+-----+-----+------------+
这条数据的话,LEFT JOIN第2步是
+----+------+-----+-----+------------+
| id | name | tid | qty | date |
+----+------+-----+-----+------------+
| 1 | 张三 | 1 | 10 | 2011-03-05 |
| 2 | 关二 | 2 | 10 | 2011-03-05 |
| 1 | 张三 | 1 | 10 | 2011-04-10 |
| 1 | 张三 | 1 | 10 | 2011-04-10 |
| 2 | 关二 | 2 | 10 | 2011-04-10 |
| 2 | 关二 | 2 | 10 | 2011-04-10 |
| 2 | 关二 | 2 | 10 | 2011-04-10 |
| 3 | 刘大 | NULL| NULL| NULL |
+----+------+-----+-----+------------+
而 INNER JOIN 第二步是
+----+------+-----+-----+------------+
| id | name | tid | qty | date |
+----+------+-----+-----+------------+
| 1 | 张三 | 1 | 10 | 2011-03-05 |
| 2 | 关二 | 2 | 10 | 2011-03-05 |
| 1 | 张三 | 1 | 10 | 2011-04-10 |
| 1 | 张三 | 1 | 10 | 2011-04-10 |
| 2 | 关二 | 2 | 10 | 2011-04-10 |
| 2 | 关二 | 2 | 10 | 2011-04-10 |
| 2 | 关二 | 2 | 10 | 2011-04-10 |
+----+------+-----+-----+------------+
是这样吗??(拜托,是不是请回复一下...不然我发帖不就没意义了)
“执行到这里时,使用了索引”
而Extra什么也没显示的意思是说
“执行到到这里时结果就出来”或者是“执行到这里时,等待与别的表进行连接”
可以这样理解吗??
SELECT *
FROM `t1` LEFT JOIN `t2` ON `id`=`tid`的结果吧。
然后和INNER JOIN的对比一下。
就是因为它上面并没有说“Extra列什么也没有代表什么意思”,所以才想问一下...
而且基本上我看过的,Extra列什么也没有的时候,type列一般都是“ALL”,而且rows列也一般都是这张表的所有数据行,所以我才会有4楼的理解
可能我4楼说的不清楚,我想表达的是“Extra列什么也没有的时候,就代表这张表的所有行都会在下一执行步骤进行运算”