Open In App

Distinct clause in MS SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values and sometimes we only want to list the different (distinct) values.

Consider a simple database and we will be discussing distinct clauses in MS SQL Server. Suppose a table has a maximum of 1000 rows constituted.The probability of repeated rows otherwise called duplicates in SQL terms might occur in the table.

In SQL Server, distinct is a term used to remove duplicates from a table.

Syntax

SELECT DISTINCT column1, column2, …
FROM table_name
WHERE conditions;

Parameter Explanation

  1. DISTINCT: It will return only unique values in the column.

Query:

CREATE TABLE Customer(
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    LastName VARCHAR(50),
    Country VARCHAR(50),
    Age int(2),
  Phone int(10)
);
-- Insert some sample data into the Customers table
INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)
VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'),
       (2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'),
       (3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'),
       (4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'),
       (5, 'Nishant. Salchichas S.A.', 'Australia', 'Spain','22','xxxxxxxxxx');
       Select * from Customer;

Output:

 

To remove the duplicates, the query must be written as follows:

Query:

select distinct
Country
from Customer

Output:

 

As there are no duplicates, the same number of rows are returned. Let us see an example having duplicates.In our database “Age” having a duplicate values. So the query should be written as follows:

Query:

select distinct
Age
from student

Output:

 

There was a duplicate Age 21 but by using distinct, the duplicates are removed. 

Consider another example of null values. Here we will create a table with the name:

Query:

CREATE TABLE my_table (
  id INT,
  name VARCHAR(50),
  age INT,
  city VARCHAR(50),
  email VARCHAR(100)
);

INSERT INTO my_table (id, name, age, city, email)
VALUES
  (1, 'John Doe', NULL, 'New York', 'john.doe@example.com'),
  (2, 'Jane Smith', 28, NULL, 'jane.smith@example.com'),
  (3, 'Bob Johnson', 35, 'Los Angeles', NULL),
  (4, 'Sarah Lee', NULL, NULL, NULL);

Output:

 

Note that the name and the city are null in the case of Aditya, so the query is written as:

Query:

select distinct 
name, age , city
from my_table

Output:

 

In the case of null values, distinct removes all the other null values and restores only one null value as shown in the output.

Note: In SQL by default, it will not default we have to mention it separately in the query.

Distinct vs Group By

There is a difference between Distinct and Group in SQL. Let’s see a sample example to see the differences.

Query:

CREATE TABLE my_table (
  id INT,
  name VARCHAR(50),
  age INT,
  city VARCHAR(50),
  email VARCHAR(100)
);

INSERT INTO my_table (id, name, age, city, email)
VALUES
  (1, 'Shubham', 35, 'New York', 'john.doe@example.com'),
  (2, 'Aditya', 28, NULL, 'jane.smith@example.com'),
  (3, 'Shubham', 35, 'Los Angeles', NULL),
  (4, 'Naveen', NULL, NULL, NULL);

Output:

 

Group By:

 

Distinct:

 



Last Updated : 18 Apr, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads