mysql except运算_sql 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> * 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>