loading..
Русский    English
02:32

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
  1. SELECT DISTINCT v_color FROM utV;

2. Using GROUP BY clause

Console
Execute
  1. 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
  1. SELECT v_color FROM utV
  2. EXCEPT
  3. SELECT 'Z';

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

Console
Execute
  1. SELECT v_color FROM utV
  2. EXCEPT
  3. 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.


Bookmark and Share
Pages 1 2 3 4
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 date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates equi-join EXCEPT exercise (-2) exercise 19 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100