SQL | Distinct Clause

Definition :

In SQL, the DISTINCT keyword is used with the SELECT statement to remove all the duplicate records and fetching only unique records.

The SELECT DISTINCT FROM statement only works with a single column or with a set of related columns.

The DISTINCT clause does not work with a set of unrelated columns.

The DISTINCT statement allows you to directly reference a column inside of a nested table.

************************************************

Distinct in SQL Server 2017: 

In 2017 it is sort value of he column by default Ascending Order.

************************************************

Using DISTINCT with SELECT:

The following example uses DISTINCT to generate a list of all unique Employee Name in the Employee table.

 
      SELECT DISTINCT FirstName  FROM Employee ORDER BY FirstName;

Syntax :

The basic syntax of DISTINCT keyword is -

      SELECT DISTINCT column1, column2,.....columnN:[Name of fields of table]
      FROM table_name : [Name of table from where we want to fetch]
      WHERE [condition]

      Note : Above query will return all the unique combination of rows in the table 
             with fields you define in select query.

Example:

Creating a Employee table :

   CREATE TABLE Employee
   (
        ID        INT      IDENTITY(1,1)   PRIMARY KEY,
        FirstName NvarChar(50),
        LastName  NvarChar(50),
        Gender    NvarChar(50),
        Salary    INT
   )
   GO

Inserting few records in table : 

   Insert into Employee values ('Ajay'    , 'Gangwar', 'Male'  , 70000)
   Insert into Employee values ('Pankaj'  , 'Kumar'  , 'Male'  , 60000)
   Insert into Employee values ('Satendra', 'Singh'  , 'Male'  , 45000)
   Insert into Employee values ('Ajay'    , 'Gangwar', 'Male'  , 70000)
   Insert into Employee values ('Pankaj'  , 'Kushwah', 'Male'  , 45000)
   Insert into Employee values ('Anamika' , 'Mathur' , 'Female', 30000)
   Insert into Employee values ('Vandana' , 'Kajla'  , 'Female', 35000)
   Insert into Employee values ('Satendra', 'Kanpur' , 'Male'  , 80000)
   GO

Results :

    +----+-----------+----------+---------+--------+
    | ID | FIRSTNAME | LASTNAME | GENDER  | SALARY |
    +----+-----------+----------+---------+--------+
    |  1 | Ajay      | Gangwar  | Male    | 70000  |
    |  2 | Pankaj    | Kumar    | Male    | 60000  |
    |  3 | Satendra  | Singh    | Male    | 45000  |
    |  4 | Ajay      | Gangwar  | Male    | 70000  |
    |  5 | Pankaj    | Kushwah  | Male    | 45000  |
    |  6 | Anamika   | Mathur   | Female  | 30000  |
    |  7 | Vandana   | Kajla    | Female  | 35000  |
    |  8 | Satendra  | Kanpur   | Male    | 80000  |
    +----+-----------+----------+---------+--------+

Now, See how the following SELECT statement returns the duplicate salary records.

     SELECT Salary FROM Employee ORDER BY Salary;

The result of above query : 

   +----------+
   | SALARY   |
   +----------+
   | 30000    |
   | 35000    |
   | 45000    |
   | 45000    |
   | 60000    |
   | 70000    |
   | 70000    |
   | 80000    |
   +----------+

Now, let us use the DISTINCT keyword with the above SELECT query and then see the result.

   SELECT DISTINCT Salary FROM Employee ORDER BY Salary;

Result:

This would produce the following result where we do not have any duplicate entry.
   +----------+
   | SALARY   |
   +----------+
   | 30000    |
   | 35000    |
   | 45000    |
   | 60000    |
   | 70000    |
   | 80000    |
   +----------+

Please Note: 

Without the keyword DISTINCT in both the above examples 8 records would have been fetched instead of 6, since in the original table there are 8 records with the duplicate values.

    SELECT DISTINCT * FROM Employee;
    +----+-----------+----------+---------+--------+
    | ID | FIRSTNAME | LASTNAME | GENDER  | SALARY |
    +----+-----------+----------+---------+--------+
    |  1 | Ajay      | Gangwar  | Male    | 70000  |
    |  2 | Pankaj    | Kumar    | Male    | 60000  |
    |  3 | Satendra  | Singh    | Male    | 45000  |
    |  4 | Ajay      | Gangwar  | Male    | 70000  |
    |  5 | Pankaj    | Kushwah  | Male    | 45000  |
    |  6 | Anamika   | Mathur   | Female  | 30000  |
    |  7 | Vandana   | Kajla    | Female  | 35000  |
    |  8 | Satendra  | Kanpur   | Male    | 80000  |
    +----+-----------+----------+---------+--------+