SQL SELECT Statement
The DISTINCT keyword is used to select only Unique records in a particular column or list of columns from the Table.
You can also refer to a column as a field so it means that the DISTINCT clause helps you to select only distinct records from a fixed list of fields.
In simple words, it will remove all the duplicates and will show only unique values in the output.
Example
SELECT JobTitle FROM HumanResources.Employee;
It gives 290 rows in the output and you can see that it has multiple duplicate records in the JobTitle Field.
So, if you wish to return only the unique values from the JobTitle, then you can use the DISTINCT Keyword –
Example
SELECT DISTINCT JobTitle FROM HumanResources.Employee;
In this case, it has only 67 records in the output.
In the same way, if i wish to select only distinct records from the OrganizationLevel field, then I can code –
Example
SELECT DISTINCT OrganizationLevel FROM HumanResources.Employee;
NULL values are missing values or unknown values in SQL. You cannot define the value for a NULL value. Do not worry about NULL values at this moment as you will understand this in detail as we move forward.
Now, I want you to think about this. I have Person table –
SELECT * FROM Person.Person;
So, for BusinessentityId 5, the Title is MS and the Suffix is NULL. Similarly, for BusinessId 13, the Title is MS and the Suffix is NULL.
As we discussed that NULL is a missing and unknown value and if i use the DISTINCT clause, then how many records of MS with NULL combination will be present in the output.
So, you might think that NULL is an unknown value so the output will have all the combinations of ‘MS.’ with NULL but it is not the case, it will only have 1 combination of ‘MS.’ with NULL.
Let us select only title and Suffix –
Example
SELECT Title, Suffix FROM Person.Person;
And you see that there are many records where the title has the value as ‘MS.’ and the suffix as NULL.
And, if is simply code –
SELECT DISTINCT Title, Suffix FROM Person.Person;
Also, if you analyze carefully, there is only 1 entry for NULL with NULL combination.