Blog

Absolutely, let’s delve into the basics of SQL, specifically focusing on the SELECT DISTINCT statement. SQL is a language used for managing data in relational databases, and SELECT DISTINCT is particularly useful when you want to retrieve unique values from a column or combination of columns.

SQL SELECT DISTINCT Statement

Purpose:

The SELECT DISTINCT statement is used to retrieve unique values from a specified column in a table. It eliminates duplicate rows from the result set, returning only distinct (different) values.

Syntax:

SELECT DISTINCT column1, column2, ...
FROM table_name;

• SELECT DISTINCT: Specifies that the query should return only distinct (unique) values.
• column1, column2, ...: The columns you want to retrieve unique values from.
• table_name: The table from which you are selecting the data.

Let’s go through some examples:

Example 1: Basic Usage

Suppose we have a simple table Students with columns ID and Name. We want to retrieve unique names from the Name column.

SQL Code:

CREATE TABLE Students (
    ID INT,
    Name VARCHAR(50)
);

INSERT INTO Students (ID, Name)
VALUES
    (1, 'John'),
    (2, 'Jane'),
    (3, 'John'),
    (4, 'Mary'),
    (5, 'Jane');

SELECT DISTINCT Name
FROM Students;

Output:

Name
-----
John
Jane
Mary

Explanation:

• We create a table Students with columns ID and Name.
• Insert some sample data into the table.
• The SELECT DISTINCT Name FROM Students; query retrieves unique names from the Name column.
• The output includes distinct names: ‘John’, ‘Jane’, ‘Mary’.

Example 2: Distinct Values from Multiple Columns

Let’s say we have a table Sales with columns Product and Price, and we want to get unique combinations of Product and Price.

SQL Code:

CREATE TABLE Sales (
    Product VARCHAR(50),
    Price DECIMAL(10, 2)
);

INSERT INTO Sales (Product, Price)
VALUES
    ('Laptop', 1200.00),
    ('Phone', 800.00),
    ('Laptop', 1200.00),
    ('Tablet', 500.00),
    ('Phone', 800.00);

SELECT DISTINCT Product, Price
FROM Sales;

Output:

Product   |  Price
------------------
Laptop    |  1200.00
Phone     |  800.00
Tablet    |  500.00

Explanation:

• We create a table Sales with columns Product and Price.
• Insert some sample data into the table.
• The SELECT DISTINCT Product, Price FROM Sales; query retrieves unique combinations of Product and Price.
• The output includes distinct pairs of Product and Price.

Example 3: Using ORDER BY with SELECT DISTINCT

You can combine SELECT DISTINCT with ORDER BY to get unique values and order them.

SQL Code:

SELECT DISTINCT Product, Price
FROM Sales
ORDER BY Price DESC;

Output:

Product   |  Price
------------------
Laptop    |  1200.00
Phone     |  800.00
Tablet    |  500.00

Explanation:

• This example is similar to Example 2 but adds an ORDER BY Price DESC clause.
• The ORDER BY Price DESC orders the result set by Price in descending order.
• The output includes distinct pairs of Product and Price, ordered by Price in descending order.

Example 4: Using WHERE with SELECT DISTINCT

You can use WHERE to filter the rows before selecting distinct values.

SQL Code:

SELECT DISTINCT Product, Price
FROM Sales
WHERE Price > 700.00;

Output:

Product   |  Price
------------------
Laptop    |  1200.00
Phone     |  800.00

Explanation:

• This example selects distinct Product and Price combinations where Price is greater than 700.00.
• The output includes distinct pairs of Product and Price where Price is greater than 700.00.

Example 5: Using COUNT with SELECT DISTINCT

You can count the number of distinct values using COUNT.

SQL Code:

SELECT COUNT(DISTINCT Product) AS TotalProducts
FROM Sales;

Output:

TotalProducts
-------------
3

Explanation:

• This example uses COUNT(DISTINCT Product) to count the number of distinct products in the Sales table.
• The output is a count of unique Product values in the Sales table.

Summary:

• SELECT DISTINCT is used to retrieve unique values from a column or combination of columns.
• It eliminates duplicate rows from the result set.
• You can use it with ORDER BY, WHERE, and other clauses to customize your results.

Learning these basics of SELECT DISTINCT in SQL will give you a good foundation for working with databases and querying unique data. Practice these examples on your preferred SQL environment to solidify your understanding!