Utilizing DISTINCT for Uniqueness Exploration

Welcome back! In the previous lesson, we explored the COUNT function for quantitative analysis in PostgreSQL. Today, we'll dive into another essential SQL feature: the DISTINCT clause.

The DISTINCT clause is used to return unique values from a column, helping you find and filter out duplicate data. For example, the characters table has 243 rows, but some of those characters appear in multiple movies. Therefore, they appear multiple times in the table. To find out how many distinct character names there are, we can use the DISTINCT function.

For convenience, the tables and columns in our Marvel movies dataset are:

Movies Table

 movie_id | movie_name | release_date | phase 
----------+------------+--------------+-------

Movie Details Table

 movie_id | budget_million_usd | box_office_million_usd | imdb_rating | runtime_minutes 
----------+--------------------+------------------------+-------------+-----------------

Characters Table

 character_id | movie_id | character_name | actor | screen_time_minutes 
--------------+----------+----------------+-------+---------------------

Let's start by understanding the syntax of the DISTINCT clause:

SELECT DISTINCT column_name
FROM table_name;

Here's a quick breakdown of the syntax:

• SELECT DISTINCT: This part of the command tells PostgreSQL that you want to select unique values from a specific column.
• column_name: Replace this with the name of the column you want to filter for unique values.
• FROM table_name: Replace this with the name of the table containing the data.

Now, let's see it in action with a practical example.

Suppose we want to get a list of unique character names. The SQL query is:

SELECT DISTINCT character_name 
FROM characters;

• SELECT DISTINCT character_name: This part selects unique character_name values from the characters table.
• FROM characters: Specifies the characters table where the data is located.

When you run this query, you will get a table of unique character names. The first 20 entries of the output are:

           character_name            
-------------------------------------
 Stephen Strange/Doctor Strange
 JARVIS
 Mysterio
 Adrian Toomes/Vulture
 Karl Mordo
 Christine Palmer
 Nebula
 Rocket
 Gamora
 Phil Coulson
 Makkari
 Flash Thompson
 Drax the Destroyer
 Gorr the God Butcher
 Ned Leeds
 Max Dillon/Electro
 Odin
 Everett Ross
 Ronan the Accuser
 Pepper Potts
....
(147 rows)

By using the DISTINCT clause, we eliminate the duplicate entries and get a list of unique character names. The output tells us that there are 147 unique character names in the characters table.