交叉连接查询
select * from dept3,emp3;
得到结果如下
还有很多,只截取了一部分。
内连接查询
实际上,内连接查询求的是两张表的交集。
-- 隐式内连接(SQL92标准):
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
-- 显示内连接(SQL99标准):
select * from dept3 inner join emp3 on dept3.deptno=emp3.dept_id; #其中 inner 可以省略
-- 隐式内连接(SQL92标准):
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
select * from dept3,emp3 where deptno = dept_id;
-- 显示内连接(SQL99标准):
select * from dept3 inner join emp3 on dept3.deptno=emp3.dept_id; #其中 inner 可以省略
-- 查询研发部门的所有成员
select * from dept3 inner join emp3 on dept3.deptno = emp3. dept_id&&name = '研发部';
-- 查看研发部和销售部的所有成员
select * from dept3 inner join emp3 on dept3.deptno = emp3. dept_id&&(name = '研发部'||name = '销售部');
select * from dept3 inner join emp3 on dept3.deptno = emp3. dept_id&&name in('研发部','销售部');
-- 查询每个部门的员工数,并升序排序
select dept3.deptno,dept3.name,count(1) from dept3 inner join emp3 on dept3.deptno=emp3.dept_id group by dept3.deptno,dept3.name order by count(1);
-- 查询人数大于等于三的部门,并按照人数降序排序
SELECT
dept3.deptno,
dept3.NAME,
count( 1 )
FROM
dept3
INNER JOIN emp3 ON dept3.deptno = emp3.dept_id
GROUP BY
dept3.deptno,
dept3.NAME
HAVING
count( 1 )>= 3
ORDER BY
count( 1 ) DESC;