Eliminating Duplicate Rows: DISTINCT and ALL

The DISTINCT and ALL keywords in the SELECT list let you specify what to do with duplicate rows in your results.

  • ALL returns all qualified rows and is the default. If you don't specify ALL or DISTINCT, ALL is assumed.

  • DISTINCT returns only unique rows.

For example, if you search for all the author identification codes in the titleauthors table, you'll find these rows:

SQL
 select au_id from titleauthors order by au_id 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 ...

Get Practical SQL Handbook, The: Using SQL Variants, Fourth Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial