参考源
- 简单教程
- 菜鸟教程
SQL right join 关键字
- SQL
right join
关键字将右表 ( table2 )所有的行返回,即是左表 ( table1 )中没有匹配。 - 如果左表中没有匹配,则结果为 NULL
select column_name(s) from table1 right join table2 on table1.column_name = table2.column_name;
在某些数据库中,
RIGHT JOIN
称为RIGHT OUTER JOIN
select column_name(s) from table1 right outer join table2 on table1.column_name = table2.column_name;
图例
图源于简单教程
https://www.twle.cn/l/yufei/sql/sql-basic-join-right.html,很好理解。
假设我们有两张表 A 和 B
可以看到每个表的最后一条记录是不匹配的,那么 RIGHT JOIN
的结果就是
示例数据
CREATE DATABASE IF NOT EXISTS hardy_db default character set utf8mb4 collate utf8mb4_0900_ai_ci;
USE hardy_db;
DROP TABLE IF EXISTS lesson;
DROP TABLE IF EXISTS lesson_views;
CREATE TABLE lesson (
id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name varchar(32) default '',
views int(11) NOT NULL default 0,
created_at DATETIME
);
CREATE TABLE lesson_views (
uniq bigint(20) primary key NOT NULL default 0 ,
lesson_id int(11) default '0',
date_at int(11) NOT NULL default 0,
views int(11) NOT NULL default 0
);
INSERT INTO lesson(id,name,views,created_at) VALUES
(1, 'Python 基础教程',981,'2017-04-18 13:52:03'),
(3, 'Ruby 基础教程',199,'2017-05-01 06:16:14'),
(4, 'SQL 基础教程', 300,'2017-05-02 08:13:16');
INSERT INTO lesson_views(uniq,lesson_id,date_at,views) VALUES
(20170511000001,1,20170511,320),
(20170511000002,2,20170511,22),
(20170511000003,3, 20170511,49),
(20170512000001,1,20170512,220),
(20170512000002,2,20170512,12),
(20170512000003,3,20170512,63),
(20170513000001,1,20170513,441),
(20170513000002,2,20170513,39),
(20170513000003,3,20170513,87);
right join使用
- 假设要查看每天的访问量,那么可以使用下面的 SQL 语句
select lesson.id,lesson.name,lesson_views.date_at, lesson_views.views from lesson right join lesson_views on lesson.id=lesson_views.lesson_id order by lesson.id desc;
输出结果如下
mysql> select lesson.id,lesson.name,lesson_views.date_at, lesson_views.views from lesson right join lesson_views on lesson.id=lesson_views.lesson_id order by lesson.id desc;
+------+---------------------+----------+-------+
| id | name | date_at | views |
+------+---------------------+----------+-------+
| 3 | Ruby 基础教程 | 20170512 | 63 |
| 3 | Ruby 基础教程 | 20170511 | 49 |
| 3 | Ruby 基础教程 | 20170513 | 87 |
| 1 | Python 基础教程 | 20170511 | 320 |
| 1 | Python 基础教程 | 20170513 | 441 |
| 1 | Python 基础教程 | 20170512 | 220 |
| NULL | NULL | 20170512 | 12 |
| NULL | NULL | 20170511 | 22 |
| NULL | NULL | 20170513 | 39 |
+------+---------------------+----------+-------+