SQL Server/T-SQL/Data Set/EXCEPT

Dataset EXCEPT

   <source lang="sql">

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>

      </source>