sql的7种join的写法(orcle+mysql)

sql中join的写法(orcle+mysql)

前言

我们以最普遍的emp和dept表为例

• 笛卡尔积查询

emp  6行数据
dept 5行数据
sql:
select*from dept d , emp e
查出来是30行(6*5=30)

1.查出e中所有,d中没对应的显示null

select*from emp e left join dept d
ON
e.dept_id = d.id

也可以这样

select*from dept d right join emp e
on
e.dept_id = d.id

2.查询d中所有,e中没对应的显示null

select*from emp e right join dept d
on
e.dept_id = d.id

3.只显示e中所有

select*from emp e left join dept d
on
e.dept_id = d.id
where d.id is null

4.只显示d中有的

select*from emp e right join dept d
on
e.dept_id = d.id
where e.id is null

5.显示e中和d中共有的

select*from emp e inner join dept d
on
e.dept_id = d.id

6.显示全部的数据

• oracle写法

select*from emp e full join dept d
on
e.dept_id = d.id

select*from emp e left join dept d
on
e.dept_id = d.id
union
select*from emp e right join dept d
on
e.dept_id = d.id

说明:这种写法,字段名查出来不是原本的字段名 可以起别名解决这种问题

• myql的写法:

select*from emp e left join dept d
on
e.dept_id = d.id
union
select*from emp e right join dept d
on
e.dept_id = d.id

7.查出e和d两个不共用的部分

• mysql中:

select*from emp e left join dept d
on
e.dept_id = d.id
where d.id = null
union
select*from emp e right join dept d
on
e.dept_id = d.id
where e.id = null

• oracle中:

oracle这种不推荐,因为列的名称变化了,可以给列起别名

SELECT*FROM emp e LEFT JOIN dept d
ON
e.dept_id = d.id
WHERE d.id IS NULL
UNION
SELECT*FROM emp e RIGHT JOIN dept d
ON
e.dept_id = d.id
WHERE e.id IS null

给列起别名

SELECT
e.id emp的id , e.name emp的员工的姓名,
d.id dept的id, d.name dept的部门的名称
FROM emp e LEFT JOIN dept d
ON
e.dept_id = d.id
WHERE d.id IS NULL
UNION
SELECT
e.id emp的id , e.name emp的员工的姓名,
d.id dept的id, d.name dept的部门的名称
FROM emp e RIGHT JOIN dept d
ON
e.dept_id = d.id
WHERE e.id IS null