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.
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
| 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 |
EXCEPT syntax.
SELECT column-names FROM table-name1 EXCEPT SELECT column-names FROM table-name1
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
| ORDERITEM |
|---|
| Id |
| OrderId |
| ProductId |
| UnitPrice |
| Quantity |
SELECT Id
FROM Product
EXCEPT
SELECT ProductId
FROM OrderItem
| Id |
|---|
| 78 |
