What does DISTINCT actually deduplicate, and how does it decide that two rows are the same?
DISTINCT removes rows that are exactly the same in every column that you selected, and it compares the values to decide that.
So how does it decide that two rows are the same? It compares the values of the selected columns, using the rules of the comparison operator, with one important exception. Two missing values count as the same, so you never see two rows that both have a missing value in the same column.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL,
city VARCHAR(80) NOT NULL,
country VARCHAR(80)
);
INSERT INTO customers (id, name, city, country) VALUES
(1, 'Ada Lovelace', 'London', 'United Kingdom'),
(2, 'Grace Hopper', 'New York', 'United States'),
(3, 'Alan Turing', 'Manchester', 'United Kingdom');
-- Each country once.
SELECT DISTINCT country FROM customers;
-- Each city and country pair once.
SELECT DISTINCT city, country FROM customers;
So there are three things about it that are worth knowing.
The first one is that it works on the whole row. If you select two columns, then two rows are duplicates only when both columns match. That is why SELECT DISTINCT city, country gives you more rows than SELECT DISTINCT city when the same city appears with two different countries.
The second one is that it is case sensitive by default, and that it follows the rules of the comparison operator. So 'London' and london are two different values, and 'London' and 'London ' with a trailing space are two different values too.
The third one is that the order of the result is not promised. If you want the rows in a steady order, you have to add an ORDER BY.
The next question in this topic asks for a simple query with it.