1. SQL语句中的表连接(Where)和过滤(Where)
2. 结合具体例子

SQL语句中的表连接(Where)和过滤(Where)

首先, 可视化的介绍下连接的各种情况, 以表1 和表2为例, 下面分别列出了各种可能的连接的情况

• 笛卡尔集: cross join
• 内连接: inner join
• 左外连接: left outer join
• 右外连接: right outer join
• 全外连接: full outer join

结合具体例子

重要的事情: 避免在`where`中写隐含的join, join要放到join语句里

下面的代码在sqlite中执行成功

create table zhihu(
real_name text,
nick_name text
);

create table weibo(
real_name text,
nick_name text
);

select * from zhihu;

select * from weibo;

insert into zhihu (real_name, nick_name)
values ("李照宇", "KL@zhihu"),
       ("马海强", "MHQ@zhihu"),
       ("迪丽热巴", "DLRB@zhihu");

insert into weibo (real_name, nick_name)
values ("李照宇", "KL@weibo"),
       ("马海强", "MHQ@weibo"),
       ("杨超越", "YCY@weibo");


-- cross join
select * from weibo, zhihu;

real_name  nick_name  real_name  nick_name
---------  ---------  ---------  ----------
李照宇        KL@weibo   李照宇        KL@zhihu
李照宇        KL@weibo   马海强        MHQ@zhihu
李照宇        KL@weibo   迪丽热巴       DLRB@zhihu
马海强        MHQ@weibo  李照宇        KL@zhihu
马海强        MHQ@weibo  马海强        MHQ@zhihu
马海强        MHQ@weibo  迪丽热巴       DLRB@zhihu
杨超越        YCY@weibo  李照宇        KL@zhihu
杨超越        YCY@weibo  马海强        MHQ@zhihu
杨超越        YCY@weibo  迪丽热巴       DLRB@zhihu

-- 隐式内联, 我们要避免的写法
select * from zhihu, weibo where zhihu.real_name = weibo.real_name;

real_name  nick_name  real_name  nick_name
---------  ---------  ---------  ---------
李照宇        KL@zhihu   李照宇        KL@weibo
马海强        MHQ@zhihu  马海强        MHQ@weibo


-- 显式内联, 结果同上,是我们推荐的写法
select * from weibo
join zhihu on weibo.real_name = zhihu.real_name;

real_name  nick_name  real_name  nick_name
---------  ---------  ---------  ---------
李照宇        KL@zhihu   李照宇        KL@weibo
马海强        MHQ@zhihu  马海强        MHQ@weibo

-- 全外连接, sqlite不支持
select * from weibo
outer join zhihu on weibo.real_name = zhihu.real_name;

Error: RIGHT and FULL OUTER JOINs are not currently supported

-- 左外连接
select * from weibo
left outer join zhihu on weibo.real_name = zhihu.real_name;

-- 右外连接不支持哟
select * from weibo
right outer join zhihu on weibo.real_name = zhihu.real_name;

right outer join zhihu on weibo.real_name = zhihu.real_name;


-- 左右其实是等价的, 右可以改成做
select * from zhihu
left outer join weibo on weibo.real_name = zhihu.real_name;

real_name  nick_name   real_name  nick_name
---------  ----------  ---------  ---------
李照宇        KL@zhihu    李照宇        KL@weibo
马海强        MHQ@zhihu   马海强        MHQ@weibo
迪丽热巴       DLRB@zhihu


-- 区分join和filter
select * from weibo
join zhihu on weibo.real_name = zhihu.real_name
where weibo.real_name = "李照宇";

real_name  nick_name  real_name  nick_name
---------  ---------  ---------  ---------
李照宇        KL@weibo   李照宇        KL@zhihu


-- 最后再强调一遍: 避免隐式join
select * from weibo, zhihu
where weibo.real_name = zhihu.real_name and weibo.real_name = "李照宇";