17:21

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

SELECT statement

Conventional set-theoretic operations and the SELECT statement

Intersect and Except page 4

EXCEPT operator excludes duplicates of rows from query result. This fact gives us another way of duplicates elimination. Let's consider available ways on the example of the following task (Painting DB is used).

Get the colors of available spray cans.

1. Using DISTINCT keyword

Console

Execute

SELECT DISTINCT v_color FROM utV;

2. Using GROUP BY clause

Console

Execute

SELECT v_color FROM utV GROUP BY v_color;

3. Using EXCEPT operator

This way is based on the idea of "subtracting" a unexisting color, ex. 'Z' from existing colors:

Console

Execute

SELECT v_color FROM utV
EXCEPT
SELECT 'Z';

Because v_color column does not allow NULL, the last query can be rewritten in more universal form:

Console

Execute

SELECT v_color FROM utV
EXCEPT
SELECT NULL;

SQL Server estimates the cost of all these queries as equal. It is not surprising in view of the fact that each of the queries accomplishes scanning the table and sorting, which are the most "heavy" operations in the query execution plan.

Pages

EXCEPT substraction duplicates Painting

Previous [Union]

[Sequence of fulfillment of UNION, EXCEPT, and INTERSECT operators] Next

Last added:

Tags

aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags

The book was updated
month ago

SQL Problems and solutions

S. I. Moiseenko

SELECT statement

Conventional set-theoretic operations and the SELECT statement

Intersect and Except page 4