SQL多表查询的内外连接查询分类及子查询

一、连接查询

1.笛卡尔积

#笛卡尔积 后面没有where 条件，会查询出很多无效的数据
SELECT p.*,g.* FROM product p,category g;

2.内连接

​ 隐式内连接（隐式内连接接条件用where，用on错误）

SELECT p.*,g.* FROM product p, category g WHERE p.cno=g.cid;
#SELECT p.*,g.* FROM product p,category g ON p.cno=g.cid;

​ 显示内连接(inner可以省略掉，where/on都可运行)

SELECT p.*,g.* FROM product p INNER JOIN category g ON p.cno=g.cid;

3.左外连接(以左表为依据)

SELECT p.*,g.* FROM product p LEFT OUTER JOIN category g on p.cno=g.cid;

4.右外连接(以右表为依据)

SELECT p.*,g.* FROM product p RIGHT OUTER JOIN category g ON p.cno=g.cid;

5.完全连接

#ORACLE里面FULL JOIN
SELECT p.*,c.* FROM product p FULL JOIN category c ON p.cno=c.cid;
# mysql 里面做完全连接UNION
SELECT p.*,g.* FROM product p LEFT OUTER JOIN category g on p.cno=g.cid 
UNION 
SELECT p.*,g.* FROM product p RIGHT OUTER JOIN category g ON p.cno=g.cid;

6.不等连接

#ON 等值连接才能使用ON
SELECT p.*,g.* FROM product p, category g WHERE p.cno!=g.cid;

7.自然连接

#当字段名称相同进行连接，并且去除掉重复的列
SELECT * FROM product p NATURAL JOIN category c;

二、子查询

# 1.查询出编号为小米手机所属的类名
#1.查询小米所在的分类编号
SELECT cno FROM product WHERE pname="小米mix4";
#2.查询该分类编号的类名
SELECT cname FROM category WHERE cid=
(SELECT cno FROM product WHERE pname="小米mix4");