Where Clause Predicates

SQL includes a between comparison operator to simplify where clauses that
specify that a value be less than or equal to some value and greater than or
equal to some other value. If we wish to find the names of instructors with salary
amounts between $90,000 and $100,000, we can use the between comparison to
write:

select name
from instructor
where salary between 90000 and 100000;

instead of:

select name
from instructor
where salary <= 100000 and salary >= 90000;

Similarly, we can use the not between comparison operator.

We can extend the preceding query that finds instructor names along with
course identifiers, which we saw earlier, and consider a more complicated case
in which we require also that the instructors be from the Biology department:
“Find the instructor names and the courses they taught for all instructors in the
Biology department who have taught some course.” To write this query, we can
modify either of the SQL queries we saw earlier, by adding an extra condition in
the where clause. We show below the modified form of the SQL query that does
not use natural join.

select name, course_id
from instructor, teaches
where instructor.ID= teaches.ID and dept_name = ’Biology’;

SQL permits us to use the notation (v1, v2,...,vn) to denote a tuple of arity n
containing values v1, v2,...,vn. The comparison operators can be used on tuples,
and the ordering is defined lexicographically. For example, (a1, a2) <= (b1, b2) is true if a1 <= b1 and a2 <= b2; similarly, the two tuples are equal if all their
attributes are equal. Thus, the preceding SQL query can be rewritten as follows:

select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, ’Biology’);

Frequently Asked Questions