【SQL】SQL中笛卡尔积、内连接、外连接的数据演示
SQL的查询语句中,常使用到内连接、外连接,以及连接的基础--笛卡尔积运算。
在简单的SQL中,也许我们还分辨清楚数据如何连接,一旦查询复杂了,脑子也犯浆糊了,迷迷糊糊的。
本文,简单以数据形式记录连接的数据结果,在迷糊时可翻阅。
以MySQL运行。
> 原始的表
select * from t_user u;
select * from t_address a;
select * from t_phone p;
> 笛卡尔积
select * from t_user, t_address; 或 select * from t_user inner join t_address;
> 内连接
-- 例3.1 select * from t_user u, t_address a where u.id = a.user_id; -- 例3.2 select * from t_user u inner join t_address a where u.id = a.user_id; -- 例3.3 select * from t_user u inner join t_address a on u.id = a.user_id;
-- 例3.4 select * from t_user u inner join t_address a where u.id = a.user_id and a.address like '%罗湖%'; -- 例3.5 select * from t_user u inner join t_address a on u.id = a.user_id and a.address like '%罗湖%';
例3.4、例3.5虽结果集相同,按照其SQL语义看,执行过程应该有所不同。(我对其中原理也不了解,此处暂不作深究)
> 左外连接
select * from t_user u left outer join t_address a on u.id = a.user_id;
多层外连接
select * from t_user u left outer join t_address a on u.id = a.user_id left outer join t_phone p on u.id = p.user_id;
select * from t_user u left outer join t_address a on u.id = a.user_id left outer join t_phone p on u.id = p.user_id left outer join t_phone p2 on u.id = p2.user_id;
> 右外连接
select * from t_user u right outer join t_address a on u.id = a.user_id;
获取这样查询,效果更好看、明显
select * from t_address a right outer join t_user u on u.id = a.user_id;