SQL Server/T-SQL/Data Set/EXCEPT

Dataset EXCEPT

33>
34>
35> -- INTERSECT: find all employee numbers that belong to the result set of the first query as well as of the second query.
36> CREATE TABLE employee  (emp_no    INTEGER NOT NULL,
37>                         emp_fname CHAR(20) NOT NULL,
38>                         emp_lname CHAR(20) NOT NULL,
39>                         dept_no   CHAR(4) NULL)
40>
41> insert into employee values(1,  "Matthew", "Smith",    "d3")
42> insert into employee values(2,  "Ann",     "Jones",    "d3")
43> insert into employee values(3,  "John",    "Barrimore","d1")
44> insert into employee values(4,  "James",   "James",    "d2")
45> insert into employee values(5,  "Elsa",    "Bertoni",  "d2")
46> insert into employee values(6,  "Elke",    "Hansel",   "d2")
47> insert into employee values(7,  "Sybill",  "Moser",    "d1")
48>
49> select * from employee
50> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
emp_no      emp_fname            emp_lname            dept_no
----------- -------------------- -------------------- -------
          1 Matthew              Smith                d3
          2 Ann                  Jones                d3
          3 John                 Barrimore            d1
          4 James                James                d2
          5 Elsa                 Bertoni              d2
          6 Elke                 Hansel               d2
          7 Sybill               Moser                d1
(7 rows affected)
1>
2>
3> CREATE TABLE department(dept_no   CHAR(4) NOT NULL,
4>                         dept_name CHAR(25) NOT NULL,
5>                         location  CHAR(30) NULL)
6>
7> insert into department values ("d1", "developer",   "Dallas")
8> insert into department values ("d2", "tester",      "Seattle")
9> insert into department values ("d3", "marketing",  "Dallas")
10>
11> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT DISTINCT emp_fname FROM employee
3> EXCEPT
4> SELECT location FROM department
5> GO
emp_fname
------------------------------
Ann
Elke
Elsa
James
John
Matthew
Sybill
(7 rows affected)
1>
2> drop table employee
3> drop table department
4> GO
1>