SQLite | Query Data | Remove Duplicate Rows with DISTINCT

Tags:

• SQLite
• Database

Query results may contain duplicate values. The DISTINCT keyword removes those duplicates. This article explains how to use it.

Removing Duplicates with DISTINCT

Use DISTINCT in a SELECT statement to exclude duplicate results. The syntax is as follows.

SELECT DISTINCT column_name, ... FROM table_name;

Duplicates are determined from the selected columns, not the entire source row. With one column, repeated values are removed; with multiple columns, repeated combinations are removed.

Use ALL to retain duplicates. The syntax is as follows.

SELECT ALL column_name, ... FROM table_name;

Omitting both keywords is equivalent to ALL, so it normally does not need to be written.

–

Create the following table for an example.

create table product (id integer, name text, color text);

sqlite> create table product (id integer, name text, color text);
sqlite> 

Insert data into the table.

insert into product values (1, 'Mouse', 'White');
insert into product values (2, 'Pen', 'Green');
insert into product values (3, 'Mouse', 'Black');
insert into product values (4, 'NotePC', 'Black');
insert into product values (5, 'Display', 'Yellow');
insert into product values (6, 'Mouse', 'White');

sqlite> insert into product values (1, 'Mouse', 'White');
sqlite> insert into product values (2, 'Pen', 'Green');
sqlite> insert into product values (3, 'Mouse', 'Black');
sqlite> insert into product values (4, 'NotePC', 'Black');
sqlite> insert into product values (5, 'Display', 'Yellow');
sqlite> insert into product values (6 'Mouse', 'White');
Error: near "'Mouse'": syntax error
sqlite> insert into product values (6, 'Mouse', 'White');
sqlite> 

First query the name column from product, including duplicates.

select name from product;

sqlite> select name from product;
name      
----------
Mouse     
Pen       
Mouse     
NotePC    
Display   
Mouse     
sqlite> 

Next, query name with DISTINCT.

select distinct name from product;

sqlite> select distinct name from product;
name      
----------
Mouse     
Pen       
NotePC    
Display   
sqlite>

Duplicate name values are excluded.

Compare queries of the color column with and without DISTINCT.

select color from product;

sqlite> select color from product;
color     
----------
White     
Green     
Black     
Black     
Yellow    
White     
sqlite> 

select distinct color from product;

sqlite> select distinct color from product;
color     
----------
White     
Green     
Black     
Yellow    
sqlite> 

With DISTINCT, duplicate color values are excluded.

Removing Duplicates Across Multiple Columns

For multiple columns, DISTINCT removes rows whose selected value combinations are identical. The following query applies it to name and color.

select distinct name, color from product;

qlite> select distinct name, color from product;
name        color     
----------  ----------
Mouse       White     
Pen         Green     
Mouse       Black     
NotePC      Black     
Display     Yellow    
sqlite> 

Individual values may repeat, but only rows with the same values in both selected columns are removed.