SQL except verbose usage

Except returns all the distinct values that are not found in the right query from the left query.

Intersect returns all the distinct values returned by the two queries on both sides of the intersect operand.

The following are the basic rules that combine the result sets of two queries that use except or intersect:

The number of columns in all queries must be the same as the order of the columns. Data types must be compatible.

Transact-SQL Syntax conventions

Grammar

{<query_specification> | (<query_expression>)}
{except | intersect}
{<query_specification> | (<query_expression>)}

Parameters
<query_specification> | (<query_expression>)
A query specification or query expression returns data that is compared to data from another query specification or query expression. In except or intersect operations, columns can be defined differently, but they must be compared after an implicit conversion. If the data type is different, the type used to perform the comparison and return the result is determined based on the rule of the data type precedence.

If the type is the same, but the precision, scale, or length are different, the results are determined based on the same rules used to merge the expressions. For more information, see Precision, Number of decimal places, and length (Transact-SQL).

The query specification or expression cannot return XML, text, ntext, image, or non-binary CLR user-defined type columns because these data types are not comparable.

Except
Returns all the distinct values not returned by the query on the right from the query to the left of the except operand

See examples

33>
34>
35>--Intersect:find All employee numbers this belong to the result set of the ' the ' the ' the ' the ' the ' the ' the ' as ' as ' the second Q Uery.
36> CREATE TABLE employee (Emp_no integer NOT NULL,
37> Emp_fname char (not NULL),
38> Emp_lname char (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 (not NULL),
5> location char () 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>