Suppose one is interested in a list of student names and the names of the Resident Advisors
in their dormitories. Here is one way to JOIN the Student and Dorm tables to combine the information:
SELECT Sname, RA
FROM Student, Dorm
WHERE Student.Dorm = Dorm.Dorm;
Conceptually, a JOIN works by concatenating the rows in the two relations where the specified test is true.
In this case, each row in the Student table is concatenated with the row in the Dorm table where the value of
the Dorm column in the Dorm table is the same as the value of the Dorm column in the Student table
(Student.Dorm is how the Dorm column in the Student table is specified. Likewise Dorm.Dorm
specifies the Dorm column in the Dorm table.). Then the selection of columns Sname and RA occurs from the
concatenated row.
CHAP. 8] DATABASE 151
Operator Meaning
= equal to
> greater than
< less than
>= greater than or equal to
<= less than or equal to
!= not equal to
!< not less than
!> not greater than
AND True if both boolean comparisons are true
OR True if either boolean expression are true
NOT Reverses the truth value of another boolean operator
IN True if the operand is one of the listed values, or one of the values returned by a subquery
LIKE True if the operand matches a pattern
% matches anything
_ (underscore) matches any one character
Other characters match themselves
BETWEEN True if the operand is within the range specified
EXISTS True if a subquery returns any rows
ALL True if all of a set of comparisons are true
ANY True if any one of a set of comparisons is true
SOME True if some of a set of comparisons is ture
Table 8-1
Another way to write the same JOIN query would be to use this syntax:
SELECT Sname, RA
FROM Student JOIN Dorm
ON Student.
Pages:
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420