SQL DISTINCT

The SQL DISTINCT command used along with the SELECT keyword retrieves only unique data entries depending on the column list you have specified after it. To illustrate the usage of the DISTINCT keyword, we'll use our Users table introduced in the previous chapters.

FirstNameLastNameDateOfBirthEmailCity
JohnSmith12/12/1969john.smith@john-here.comNew York
DavidStonewall01/03/1954david@sql-tutorial.comSan Francisco
SusanGrant03/03/1970susan.grant@sql-tutorial.comLos Angeles
PaulO'Neil09/17/1982paul.oneil@pauls-email.comNew York
StephenGrant03/03/1974sgrant@sgrantemail.comLos Angeles

Our Users table has several users in it, and it would be interesting to retrieve a list with all cities where our users live. If we use the statement below, we will get our city list, but there will be repetitions in it, because some in some cases more than one user lives in certain city:

SELECT City FROM Users

So how do we do get a list with all cities without repeating them? As you have guessed we'll use the DISTINCT keyword:

SELECT DISTINCT City FROM Users

The result of the SQL DISTINCT expression above will look like this:

City
New York
San Francisco
Los Angeles

Essentially what the DISTINCT keyword does is removing the duplicates from the result set returned by your SELECT SQL statement.

You can use the DISTINCT keyword with more than one column. Please consider the example below:

SELECT DISTINCT LastName, City FROM Users

What the above statement will do is to return all unique combinations between LastName and City columns. Here is what the result of this statement will be:

LastNameCity
SmithNew York
StonewallSan Francisco
GrantLos Angeles
O'NeilNew York

If you have a look at the original table above, you'll notice that there are two users with identical names (Grant), who happen to live in the same city (Los Angeles). Because the combination of LastName and City values for both this users is not unique, we got only one row with it, when we used the DISTINCT keyword. On the other hand if we add one more column (Email) after the DISTINCT keyword:

SELECT DISTINCT LastName, Email, City FROM Users

We'll retrieve both users with last name Grant, simply because they have different emails and thus their entries are unique as far as our SQL statement is concerned:

LastNameEmailCity
Smithjohn.smith@john-here.comNew York
Stonewalldavid@sql-tutorial.comSan Francisco
Grantsusan.grant@sql-tutorial.comLos Angeles
O'Neilpaul.oneil@pauls-email.comNew York
Grantsgrant@sgrantemail.comLos Angeles

If your site receives plenty of queries, you should try getting a good managed hosting provider just to make sure everything goes smoothly as it should.