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.
