How does Select Distinct work on a Primary Key in Marketing cloud's SQL?

Question

How does Marketing Cloud's SQL in Automation Studio works when having a DISTINCT operator on a data extensions that has a Primary Key (in my case "Customer_Key")? Specifically, does it look for unique primary key values, and ignores the duplicate primary keys form the end-result, or is it looking at the whole data extension for duplicates (i.e. duplicate rows)? If the answer is the latter, how then I can make MC's DISTINCT only apply to primary key?

Here's a simplified extract from my code: SELECT EmailAddress,First_Name,Last_Name,Address_ID, Customer_Key(PRIMARY KEY) FROM table_1

Answer 1

Regardless whether in Marketing Cloud or outside, this is how DISTINCT works:

-SELECT DISTINCT returns only distinct (different) values.

-SELECT DISTINCT eliminates duplicate records from the results.

-DISTINCT can be used with aggregates: COUNT, AVG, MAX, etc.

-DISTINCT operates on a single column. DISTINCT for multiple columns is not supported.

Source: https://www.dofactory.com/sql/select-distinct

This mens it will only make sense to use distinct with queries like SELECT DISTINCT Customer_Key FROM table_1. If you use it in a query with more columns, it will only deduplicate rows where all field values are identical.

To be able to deduplicate queries with multiple columns, use the GROUP BY clause: https://www.w3schools.com/sql/sql_groupby.asp