Eliminating duplicate query results with distinct

The optional distinct keyword eliminates duplicate rows from the default results of a select statement.

For compatibility with other implementations of SQL, Adaptive Server syntax allows the use of all to explicitly ask for all rows. The default for select statements is all. If you do not specify distinct, you will get, by default, all rows including duplicates.

For example, here is the result of searching for all the author identification codes in the titleauthor table without distinct:

select au_id 
from titleauthor 
au_id 
----------- 
172-32-1176 
213-46-8915 
213-46-8915 
238-95-7766 
267-41-2394 
267-41-2394 
274-80-9391 
409-56-7008 
427-17-2319 
472-27-2349 
486-29-1786 
486-29-1786 
648-92-1872 
672-71-3249 
712-45-1867 
722-51-5454 
724-80-9391 
724-80-9391 
756-30-7391 
807-91-6654 
846-92-7186 
899-46-2035 
899-46-2035 
998-72-3567 
998-72-3567 
 
(25 rows affected) 

There are some duplicate listings. Use distinct to eliminate them.

select distinct au_id 
from titleauthor 
au_id 
----------- 
172-32-1176 
213-46-8915 
238-95-7766 
267-41-2394 
274-80-9391 
409-56-7008 
427-17-2319 
472-27-2349 
486-29-1786 
648-92-1872 
672-71-3249 
712-45-1867 
722-51-5454 
724-80-9391 
756-30-7391 
807-91-6654 
846-92-7186 
899-46-2035 
998-72-3567 
 
(19 rows affected)  

distinct treats multiple null values as duplicates. In other words, when distinct is included in a select statement, only one NULL is returned, no matter how many null values are encountered.

When used with the order by clause, distinct can return multiple values. See “order by and group by used with select distinct” for more information.