Oracle 左连接、右连接

 

-- 人员和部门一一对应
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;