Oracle 左连接、右连接

gcbdiebiel

　　

-- 人员和部门一一对应
select s.name, d.dept_name from t_stu s, t_dept d where 1 = 1 and s.dept_id = d.dept_id;
-- 统计人员，包括没有部门的人员
select s.* from t_stu s, t_dept d where 1 = 1 and s.dept_id = d.dept_id(+);
-- 统计部门，包括没有人员的部门
select d.dept_name, s.stu_id from t_stu s, t_dept d where 1 = 1 and s.dept_id(+) = d.dept_id;
 
SELECT tb.gender, COUNT(1) FROM t_employee tb WHERE 1 = 1 GROUP BY tb.gender ORDER BY tb.birthday;
-- 27532
SELECT COUNT(1) FROM t_employee tb WHERE 1 = 1;
-- 146257
SELECT COUNT(1) FROM t_dept tb WHERE 1 = 1;
-- 假设 t_employee 都有 dept_id
SELECT COUNT(1)
FROM t_employee emp, t_dept dept
WHERE 1 = 1
AND emp.dept_id = dept.dept_id(+);
SELECT emp.emp_id, dept.dept_id
FROM t_employee emp, t_dept dept
WHERE 1 = 1
AND emp.dept_id(+) = dept.dept_id;