A NULL value literally has no value. Since the value does not exist, one cannot test for equality of the value to
any other, even to NULL. When testing for NULL values, always use IS NULL or IS NOT NULL.
Suppose one wanted to know how many students participated in each club? SQL has built-in functions for
simple and frequently needed math functions. The standard five functions are COUNT, SUM, AVG, MIN, and
MAX, and many DBMS vendors provide additional nonstandard functions as well. One could report the count of
students in each club this way:
SELECT Cname, COUNT(*) AS Membership
FROM ClubMembership
GROUP BY Cname
ORDER BY Cname;
COUNT(*) says count the occurrences of rows. The AS Membership phrase sets the column
heading for the counts in the report to Membership. The GROUP BY phrase tells SQL how to break down
the calculations when using the built-in function; that is, count rows (members) for each club.
If there were clubs that no students had joined, and one wanted to include that information in the report,
one could use an outer join along with the built-in count function to achieve the desired report:
SELECT Club.Cname, COUNT(ClubMembership.Cname) AS Membership
FROM Club LEFT JOIN ClubMembership
ON Club.
Pages:
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424