8
SELECT Sname, Major, Cname
FROM Student LEFT JOIN ClubMembership
ON Student.Sname = ClubMembership.MemberName;
The word LEFT says that the table on the left, Student, not ClubMembership, is the one for which
all rows will be reported. Now the query will return one row for every student (more than one for students who
belong to more than one club), and if a student is not a member of any club, the Cname column will be NULL.
The word RIGHT can be used to affect the rows reported from the table on the right. Rearranging the order
of tables in the join, and switching to the word RIGHT, gives a new query that reports the same result:
SELECT Sname, Major, Cname
FROM ClubMembership RIGHT JOIN Student
ON Student.Sname = ClubMembership.MemberName
If one wants to include all rows from both tables, regardless of whether the join condition is satisfied, use
the word FULL instead of LEFT or RIGHT.
To report only those students who are not members of any club, simply add a WHERE clause:
SELECT Sname, Major, Cname
FROM Student LEFT JOIN ClubMembership
ON Student.Sname = ClubMembership.MemberName
WHERE Cname IS NULL;
Notice that one uses the word IS, instead of the equal sign, to test for the presence of a NULL value.
Pages:
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423