left join现象之一，出现null

最新推荐文章于 2024-01-16 11:34:17 发布

cswolf

最新推荐文章于 2024-01-16 11:34:17 发布

阅读量8.4k

点赞数

分类专栏： mysql 文章标签： mysql left join

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

在一次MySQL练习中，我通过如下sql语句创建数据库表shop及插入相关语句：

CREATE TABLE shop (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
(3,'C',1.69),(3,'D',1.25),(4,'D',19.95),(5,'xiaowy',20.4);

然后执行下列语句：

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;

出现如下结果：

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0005 | xiaowy | 20.40 |
+---------+--------+-------+

发现会出现s2中的article,dealer,price有NULL值

执行如下语句：

select s1.article,s1.dealer,s1.price from shop s1 right join shop s2 on s1.price < s2.price where s1.article is null;

出现如下结果：

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    NULL | NULL   |  NULL |
+---------+--------+-------+

总有一行空值

执行如下语句：

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);
有一下结果：

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
|    0005 | xiaowy | 20.40 |
+---------+--------+-------+

这里不知如何解释

MySQL帮助上说以上语句等价于：

SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price;