SELECT with DISTINCT

If a query returns a result that contains duplicate rows, the duplicates can be removed to produce a result set in which every row is unique.  To do this, include the clause DISTINCT after SELECT and before the column list.  Caution needs to be present when using DISTINCT due to the fact that DISTINCT will compare whole rows when processing.  In the case below, the query results in a set that includes duplicate rows: 

mysql> SELECT Continent FROM Country; 

+--------------------+ 

| Continent          | 

+--------------------+ 

| Asia                  | 

| Europe             | 

| North America | 

| Europe             | 

| Africa                | 

| Oceania           | 

       ...      

| Antarctica        | 

| Oceania           | 

+--------------------+ 

239 rows in set (#.## sec) 

By adding DISTINCT to the statement, the duplicates are removed and only unique rows are returned: 

mysql> SELECT DISTINCT Continent FROM Country; 

+--------------------+ 

| Continent          | 

+--------------------+ 

| Asia                   | 

| Europe              | 

| North America  | 

| Africa                 | 

| Oceania            | 

| South America | 

| Antarctica         | 

+--------------------+ 

7 rows in set (#.## sec) 

DISTINCT NULL Values 


DISTINCT treats all NULL values within a given column as having the same value. Suppose that a table t contains the following rows: 

mysql> SELECT i, j FROM t; 

+--------------+ 

|    i  |      j     | 

+--------------+ 

|    1 |    2      | 

|    1 | NULL | 

|    1 | NULL | 

+--------------+ 

For purposes of  DISTINCT, the NULL values in the second column are the same, so the second and third rows are identical.  Adding DISTINCT to the query eliminates one of them as a duplicate: 

mysql> SELECT DISTINCT i, j FROM t; 

+--------------+ 

|    i   |     j     | 

+--------------+ 

|    1 |    2      | 

|    1 | NULL | 

+--------------+