7.6. The Difference Operation

Because SQL Server does not support the MINUS predicate, we will show the set difference operation using a NOT..IN with two examples.

7.6.1. Example 1

Suppose that set A is the set of students in classes 2, 3, or 4 and set B is the set of students in class 2. We could use the NOT..IN predicate to remove the students in set B from set A (a difference operation) by typing the following query:

    SELECT sname, class
    FROM   Student
    WHERE class IN (2,3,4)
      AND NOT class IN (2)

which produces the following output (17 rows):

    sname                class
    -------------------- ------
    Mary                 4
    Kelly                4
    Donald               4
    Chris                4
    Jake                 4
    Susan                3
    Monica               3
    Phoebe               3
    Holly                4
    Rachel               3
    Jerry                4
    Cramer               3
    Harrison             4
    Francis              4
    Losmith              3
    Gus                  3
    Benny                4

    (17 row(s) affected)

7.6.2. Example 2

To illustrate another difference operation, we will use views with the NOT..IN to give the effect of a difference operation. Suppose for example, you wanted to find the names of those students who do not major in COSC or MATH but delete from that set those students who have made an A in some course.

First, using the NOT..IN, we will create a view (view1) of the names and majors of the students who are not COSC or MATH majors using the following query:

    CREATE VIEW view1 AS
    SELECT sname, major
    FROM   Student
    WHERE  major NOT IN ('COSC', 'MATH')

View1 will have the same 28 rows of output as shown earlier in this chapter.

Then, using the IN predicate, we will create another view (view2) of names and majors of students who have received As using the following query:

    CREATE VIEW view2 AS
    SELECT Student.sname, Student.major
    FROM   Student
    WHERE  Student.stno IN
      (SELECT g.student_number
       FROM   Grade_report g
       WHERE  g.grade = 'A')

If we type:

    SELECT *
    FROM view2;

We get the following 14 rows of output:

    sname                major
    -------------------- -----
    Lineas               ENGL
    Mary                 COSC
    Brenda               COSC
    Richard              ENGL
    Lujack               COSC
    Donald               ACCT
    Lynette              POLY
    Susan                ENGL
    Holly                POLY
    Sadie                MATH
    Jessica              POLY
    Steve                ENGL
    Cedric               ENGL
    Jerry                COSC

    (14 row(s) affected)

Then, to find those students who are not majoring in COSC or MATH, and remove from that set those who made an A in some course, the difference operation could be approached using the NOT..IN as follows, using the views created earlier:

    SELECT sname
    FROM view1
    WHERE sname NOT IN
      (SELECT sname
      FROM view2)

This query produces the following output (19 rows):

    sname
    --------------------
    Ken
    Romona
    Harley
    Chris
    Bill
    Phoebe
    Rachel
    George
    Cramer
    Fraiser
    Harrison
    Francis
    Smithly
    Sebastian
    Losmith
    Genevieve
    Lindsay
    Gus
    Benny

    (19 row(s) affected)

This query has the same effect as view1--view2 (all students who are not majoring in COSC or MATH, MINUS students who made an A in some course).