使用非等值连接查询记录-SQL语言入门知识

用非等连接查询记录 * ENAME SAL GRADE ---------- --------- --------- JAMES 950 1 SMITH 800 1 ADAMS 1100 1 ... 14 rows selected. SQL> SELECT e.ename, e.sal, s.grade 2 FROM emp e, salgrade s 3 WHERE e.sal 4 BETWEEN s.losal AND s.hisal; * SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal; Non-Equijoins (continued) The slide example creates a non-equijoin to evaluate an employee’s salary grade. The salary must be between any pair of the low and high salary ranges. It is important to note that all employees appear exactly once when this query is executed. No employee is repeated in the list. There are two reasons for this: None of the rows in the salary grade table contain grades that overlap. That is, the salary value for an employee can only lie between the low salary and high salary values of one of the rows in the salary grade table. All of the employees’ salaries lie within the limits provided by the salary grade table. That is, no employee earns less than the lowest value contained in the LOSAL column or more than the highest value contained in the HISAL column. Note: Other operators such as <= and >= could be used, but BETWEEN is the simplest. Remember to specify the low value first and the high value last when using BETWEEN. Table aliases have been specified for performance reasons, not because of possible ambiguity. Instructor Note Explain that BETWEEN … AND … are actually translated by Oracle server to a pair of conditions (a >= lower limit) and (a <= higher limit) and IN ( … ) is translated by Oracle server to a set of OR conditions (a = value1 OR a = value2 OR a = value3 ). So using BETWEEN … AND … , IN(…) has no performance benefits and can be used for logical simplicity.