7.14 Working with Duplicates
7.14.1 Problem
You've just imported a new set of rows
into
your buffer tables. However, you forgot to delete the previous
import. Now you have a table with some duplicate rows. You need a set
of tools to eliminate these duplicates and to prevent them from
occurring in the future.
Let's say that you uploaded some data from your
sales agent to the Bookstore table; however, due to a mistake in the
code, the upload doubled some rows:
BookId Name Quantity Price Type
----------- ------------------------ ----------- ------- ----------
1 Software Engineering 5 15.00 Manual
2 Modern Operating Systems 7 20.00 Reference
3 Learn SQL 15 18.00 Textbook
4 Learn Advanced SQL 1 8.00 Textbook
5 JavaScript Tutorial 5 10.00 Textbook
6 Modern Operating Systems 7 20.00 Reference
7 Learn SQL 15 18.00 Textbook
7.14.2 Solution
We'll demonstrate a technique for dealing with
duplicates on the Bookstore table. Please note that the BookId column
is always unique and that it is used for identifying otherwise
duplicated rows. This will be useful when only one representative of
a duplicated set needs to be singled out.
The queries in this recipe show different ways to count duplicate
rows, report on duplicate rows, extract nonduplicated rows, and
eliminate duplicate rows.
7.14.2.1 Reduction
The term reduction refers to
the
elimination of duplicate rows. The following query returns all
nonduplicate rows from the Bookstore table:
SELECT DISTINCT * FROM Bookstore
Because the DISTINCT keyword was used, any two rows containing the
same set of values will be combined into one row in the
query's result set. Because this query includes the
primary-key column BookId, it's of no practical use
in our particular case; some books still appear twice.
7.14.2.2 Selective reduction
Selective reduction is reduction that
is performed only on a
subset of the columns in a table. The following query retrieves each
unique combination of name, price, and color from the Stock table:
SELECT DISTINCT Name, Type, Price FROM Bookstore
The table stores five different books:
Name Type Price
---------------------------------------- -------------------- ---------
JavaScript Tutorial Textbook 10.00
Learn Advanced SQL Textbook 8.00
Learn SQL Textbook 18.00
Modern Operating Systems Reference 20.00
Software Engineering Manual 15.00
This time, because we did not include the always unique primary key,
each book is listed only once.
7.14.2.3 Selecting duplicates
The following query shows how you
can
find duplicate name, price, and color combinations:
SELECT Name, Type, Price
FROM Bookstore
GROUP BY Name, Type, Price
HAVING count(*) > 1
The HAVING clause in this query ensures that if a given name, price,
and color combination is only represented once, it will not be listed
in the results from this query. Any such combinations that occur more
than once will be listed:
Name Type Price
---------------------------------------- -------------------- ---------
Learn SQL Textbook 18.00
Modern Operating Systems Reference 20.00
7.14.2.4 Counting occurrences
The following query produces a report showing
the number of occurrences of each
combination of values in a given set of columns. The results are
sorted in order of highest occurrence.
SELECT Name, Type, Price, count(*) Count
FROM Bookstore
GROUP BY Name, Type, Price
ORDER BY count(*) DESC
The query lists all combinations of name, price, and type. It also
lists the number of times each combination occurs. Combinations with
the greatest number of occurrences will be listed first:
Name Type Price Count
------------------------- -------------------- ------------ -----------
Learn SQL Textbook 18.00 2
Modern Operating Systems Reference 20.00 2
JavaScript Tutorial Textbook 10.00 1
Learn Advanced SQL Textbook 8.00 1
Software Engineering Manual 15.00 1
To see the same information, but only for cases where the occurrence
count is greater than 1, use the following variation on the query:
SELECT Name, Type, Price, count(*) Count
FROM Bookstore
GROUP BY Name, Type, Price
HAVING count(*) > 1
ORDER BY count(*) DESC
This second version of the query uses a HAVING clause to restrict the
results to only those combinations of name, price, and type that
occur multiple times:
Name Type Price Count
------------------------ -------------------- ------------ -----------
Learn SQL Textbook 18.00 2
Modern Operating Systems Reference 20.00 2
7.14.2.5 Selecting by number of occurrences
The following query selects duplicates over a subset of columns where
the occurrence count is at least 3:
SELECT Name, Type, Price, count(*) Count
FROM Bookstore
GROUP BY Name, Type, Price
HAVING count(*) >= 3
You can replace the constant 3 in this query with any other
occurrence count threshold of interest.
7.14.2.6 Selecting nonduplicates
The following query selects nonduplicated rows over a subset of
columns, ignoring the ones that are duplicated:
SELECT Name, Type, Price
FROM Bookstore
GROUP BY Name, Type, Price
HAVING count(*)= 1
In this example, rows representing id, name, and qty combinations
that occur more than once will not be returned in the result set:
Name Type Price
------------------------ -------------------- ------------
JavaScript Tutorial Textbook 10.00
Learn Advanced SQL Textbook 8.00
Software Engineering Manual 15.00
7.14.2.7 Selecting duplicates with an odd occurrence count
If you're interested in duplicates where the
occurrence count is an odd number other than 1, use a query such as
this:
SELECT Name, Type, Price, count(*) Count
FROM Bookstore
GROUP BY Name, Type, Price
HAVING count(*) % 2 = 1 AND count(*)>1
Since 1 is considered an odd number, but does not imply duplication,
the WHERE clause specifically excludes rows with an occurrence count
of 1.
7.14.2.8 Selecting duplicates with an even occurrence count
A similar query can be used to retrieve duplicates where the
occurrence count is an even number:
SELECT Name, Type, Price, count(*) Count
FROM Bookstore
GROUP BY Name, Type, Price
HAVING count(*) % 2 = 0
Note that there is no need to specifically exclude rows with an
occurrence count of 1, because 1 is not an even number.
7.14.2.9 Deleting duplicate rows
You may find yourself faced with a situation where you want to
arbitrarily delete duplicate data from a table. Data like this is
often the result of running an import procedure twice. As long as you
have at least one unique value, such as a primary key value, to
identify each row, you can arbitrarily delete duplicates using a
single DELETE statement. The statement in the following example
deletes duplicate rows, arbitrarily retaining one record for each
fruit and color combination:
DELETE
FROM Warehouse
WHERE BookId NOT IN (
SELECT MAX(BookId)
FROM Bookstore
GROUP BY Name, Type
HAVING COUNT(*) > 1)
The subquery in this example identifies the highest ID number value
for each name and color combination. This identifies the row that we
are arbitrarily going to keep. All other rows with that name and
color combination are deleted. The key here is that the ID is unique
for all rows in a name and type combination.
7.14.2.10 Preventing duplicates
To ensure that no duplicates can
be
inserted into a table, create a unique index using the UNIQUE INDEX
clause, or create a UNIQUE constraint while creating a table. For
example, the following statement creates an index named BookstoreInd
that does not allow any two rows in the Bookstore table to have the
same combination of values for the Name, Type, and Price columns:
CREATE UNIQUE INDEX BookstoreInd on Bookstore
(Name, Type, Price)
7.14.3 Discussion
SQL has built-in support for enforcing an occurrence count of 1 on a
table. This support can be in the form of a UNIQUE constraint or a
unique index on the table. To enforce an occurrence count other than
1, you'll need to use triggers.
When using the SELECT DISTINCT clause in a SELECT statement, you
should be very careful about performance. At first glance, SELECT
DISTINCT may seem like a good tool to use; in reality, such a
statement can be very expensive to execute. Using the DISTINCT
keyword in a SELECT statement causes a sorting operation to occur
when such query is run. The only way that SQL Server can identify
distinct values is to first sort all the results enough to group
duplicates together. Then only one value, the
distinct value, is returned from each set of
duplicates. Because of the performance impact of the sort, you should
avoid the use of DISTINCT unless it is absolutely necessary. The
following query shows a typical example of the DISTINCT keyword being
used unnecessarily. The query uses a subquery within an EXISTS
predicate to check for rows in table B that correspond to those in
table A:
SELECT * FROM A
WHERE EXISTS(
SELECT DISTINCT *
FROM B
WHERE B.id=A.id)
This code returns the correct results, but the use of DISTINCT in the
subquery hurts performance. This is because DISTINCT forces a sort to
occur for each execution of the subquery. Since the subquery is a
correlated subquery — executed for each row — the result is a
lot of sort activity.
The purpose of the EXISTS predicate is to test for the existence of
at least one row by executing a subquery. Since only one row is
needed to cause a TRUE result to be returned,
there's no point in using DISTINCT. It
doesn't matter if more than one row satisfies the
subquery: one is enough . By omitting DISTINCT, you allow the
optimizer to stop when that one row is found.
|
