SQL EXCEPT

EXCEPT combines the results of two SELECT queries.

EXCEPT returns rows from the first query that are not in the second query.

The data type and order of columns in the two queries must match.

SQL EXCEPT

Example

#

List all products with a price less than $10.

SELECT Id, ProductName, UnitPrice
  FROM Product
EXCEPT
SELECT Id, ProductName, UnitPrice
  FROM Product
 WHERE UnitPrice >= 10
Result:  12 records
Id ProductName UnitPrice
19 Teatime Chocolate Biscuits 9.20
23 Tunnbröd 9.00
24 Guaraná Fantástica 4.50
33 Geitost 2.50
41 Jack's New England Clam Chowder 9.65
45 Rogede sild 9.50
47 Zaanse koeken 9.50
52 Filo Mix 7.00
54 Tourtière 7.45
75 Rhönbräu Klosterbier 7.75
78 Stroopwafels 9.75

Syntax

EXCEPT syntax.

SELECT column-names
  FROM table-name1 
EXCEPT
SELECT column-names
  FROM table-name1 

More Examples

EXCEPT, two different tables

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
ORDERITEM
Id
OrderId
ProductId
UnitPrice
Quantity
Problem: List all product Ids of unsold products.
SELECT Id
  FROM Product
EXCEPT
SELECT ProductId 
  FROM OrderItem
Result:  1 record
Id
78

You may also like



Guides


vsn 3.1