文件名称：用非等值连接返回记录-复杂查询技术-连接
文件大小：671KB
文件格式：PPT
更新时间：2024-05-15 15:58:10
sql
用非等值连接返回记录 SELECT e.last_name, e.salary, j.grade_level FROM employees e, job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal; … * 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 job grade table contain grades that overlap. That is, the salary value for an employee can lie only 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 job grade table. That is, no employee earns less than the lowest value contained in the LOWEST_SAL column or more than the highest value contained in the HIGHEST_SAL column. Note: Other conditions, such as <= and >= can 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 in the slide example for performance reasons, not because of possible ambiguity. Instructor Note Explain that BETWEEN … AND … is actually translated by the Oracle server to a pair of AND conditions (a >= lower limit) and (a <= higher limit) and IN ( … ) is translated by the 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; the benefit is logical simplicity.