Utilizing DISTINCT for Uniqueness Exploration

Hello again! I hope you enjoyed the previous unit on understanding and using the COUNT function. Today, we're going to expand your SQL knowledge further by introducing a new concept: the DISTINCT keyword in SQL. While DISTINCT is not strictly speaking a function, it's a powerful keyword/clause that will help you strengthen your data querying skills.

When handling data, especially large datasets, we often encounter duplicate values. The DISTINCT keyword helps us remove these duplicates and present a clean, unique list of values. Isn't that handy?!

This concept is common in many areas of life. Imagine you're trying to create a list of all the product categories offered in an online shop. If there are multiple products in the same category, should we list the category multiple times? Of course not! We use distinct or unique values, and SQL provides the DISTINCT keyword to do just that with our data.

As we learn SQL in the context of an online shopping dataset, we'll be using DISTINCT to explore unique product categories.

Given the diverse range of products in online shopping, utilizing DISTINCT is quite handy. We can use it to identify unique categories, and much more. It will broaden our understanding of the products data.

Let's first learn the format of a basic SQL query that uses DISTINCT.

SELECT DISTINCT column_name FROM table_name;

It is time to apply this format to our online shopping dataset. Consider the following statement:

SELECT DISTINCT category_id FROM Products;

-- Output:
--  category_id
-- ------------
--           1
--           2
--           3
--           4
--           5

This query will fetch all the distinct or unique category_id from the Products table. We use DISTINCT to avoid getting repeated category IDs in the output.

Let's look at a few more examples using the DISTINCT keyword.

What if we want to know all the unique products that have been ordered from our online shop? Simple, we would run:

SELECT DISTINCT product_id FROM OrderItems;

-- Sneak peek of the output:
--  product_id
-- ------------
--           1
--           6
--           7
--          10

The DISTINCT keyword can be applied to multiple columns to find unique combinations of values. For example, let's suppose you want to find all unique combinations of customer_id and order_status in the Orders table:

SELECT DISTINCT customer_id, order_status 
FROM Orders;

-- Sneak peek of the output:
-- | customer_id | order_status |
-- |-------------|--------------|
-- |          41 | Delivered    |
-- |          16 | Processed    |
-- |          44 | Refunded     |
-- |           6 | Processed    |

This query returns unique pairs of customer_id and order_status. Rows with identical customer_id values but different order_status values will be treated as distinct.

When using DISTINCT, it's important to remember that fetching unique values from large datasets can be time-consuming and slow down your queries. Therefore, always consider the performance implications and use DISTINCT only when necessary.