Mastering Oracle SQL学习笔记(join句法专题第四部份)

2008-06-14 16:30:06

2.4.2 Equi-Joins 对 Non-Equi-Joins

在Join 查询中如果使用等号操作符把两个表关联起来则为Equi-Joins,不是用等号操作符则为Non-Equi-Joins

Equi-Joins:

SELECT s.name supplier_name, p.name part_name

FROM supplier s JOIN part p

ON s.supplier_id = p.supplier_id;

Non-Equi-Joins:

SELECT p.name part_name, c.inv_class inv_class

FROM part p JOIN inventory_class c

ON p.unit_cost BETWEEN c.low_cost AND c.high_cost;

2.4.3 Self Joins

就是一个表的行与该表的另外的行关联

1) SELECT e.lname employee, m.lname manager

FROM employee e JOIN employee m

ON e.manager_emp_id = m.emp_id;

Self outer joins:

SELECT e.lname employee, m.lname manager

FROM employee e LEFT OUTER JOIN employee m

ON e.manager_emp_id = m.emp_id;

Self non-equi-joins

1) SELECT d1.name team1, d2.name team2

FROM department d1 JOIN department d2

ON d1.dept_id != d2.dept_id;

2) SELECT d1.name team1, d2.name team2

FROM department d1 JOIN department d2

ON D1.DEPT_ID < D2.DEPT_ID;

相关文章

文章评论