In this article, we will explain the SQL SELECT DISTINCT Statement.
1. Introduction
The SQL DISTINCT keyword is used to eliminate the duplicate records and return only unique records. Here is the SELECT DISTINCT syntax:
SELECT DISTINCT {column1}, {column2}, ...
FROM {table_name}
[WHERE {condition}]2. Technologies Used
The example code in this article was built and run using:
3. Install MySQL
In this step, I will install MySQL as a Docker container.
3.1 MySQL Docker Container
I will use the docker run command to get and start the latest MySQL image.
docker run
PS C:\MaryZheng\DockerImages> docker run mysql/mysql-server:latest
Here is the output from the docker run command. We will login in with the generated root password at line 31.
docker run output
PS C:\MaryZheng\DockerImages> docker run mysql/mysql-server:latest Unable to find image 'mysql/mysql-server:latest' locally latest: Pulling from mysql/mysql-server 8969f19fb2cc: Pull complete 18ff34a960f0: Pull complete 1059844cbb8f: Pull complete 3bd4cb0b78d1: Pull complete 901b41fa66ef: Pull complete b33be9f4a1f3: Pull complete 38b3da6a86f7: Pull complete Digest: sha256:5241f7de0483a70f5856da995fea98904cfce8f1c51734b7f3836c1663eead17 Status: Downloaded newer image for mysql/mysql-server:latest [Entrypoint] MySQL Docker Image 8.0.26-1.2.4-server [Entrypoint] Initializing database [Entrypoint] No password option specified for new database. [Entrypoint] A random onetime password will be generated. 2021-08-13T22:29:53.659807Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.26) initializing of server in progress as process 16 2021-08-13T22:29:53.748847Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2021-08-13T22:29:56.814494Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2021-08-13T22:30:00.791023Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main 2021-08-13T22:30:00.797107Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main 2021-08-13T22:30:01.001230Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. [Entrypoint] Database initialized 2021-08-13T22:30:09.159742Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.26) starting as process 61 2021-08-13T22:30:09.194590Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2021-08-13T22:30:09.467679Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2021-08-13T22:30:09.934172Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main 2021-08-13T22:30:09.934841Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main 2021-08-13T22:30:09.937285Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2021-08-13T22:30:09.937811Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 2021-08-13T22:30:09.996348Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: /var/run/mysqld/mysqlx.sock 2021-08-13T22:30:09.996424Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.26' socket: '/var/lib/mysql/mysql.sock' port: 0 MySQL Community Server - GPL. Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it. [Entrypoint] GENERATED ROOT PASSWORD: se7Ue3%%xj1N8/7*_8WS&u7VG?bwC3j. [Entrypoint] ignoring /docker-entrypoint-initdb.d/* 2021-08-13T22:30:17.091756Z 11 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.26). 2021-08-13T22:30:19.518493Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.26) MySQL Community Server - GPL. [Entrypoint] Server shut down [Entrypoint] Setting root user as expired. Password will need to be changed before database can be used. [Entrypoint] MySQL init process done. Ready for start up. [Entrypoint] Starting MySQL 8.0.26-1.2.4-server 2021-08-13T22:30:20.508578Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.26) starting as process 1 2021-08-13T22:30:20.580632Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2021-08-13T22:30:21.099594Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2021-08-13T22:30:21.522876Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main 2021-08-13T22:30:21.523211Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main 2021-08-13T22:30:21.527309Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2021-08-13T22:30:21.528026Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 2021-08-13T22:30:21.616243Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock 2021-08-13T22:30:21.617000Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.26' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
3.2 Start MySql Client
In this step, I will start the MySQL client with a bash command.
docker exec -it mysql bash
PS C:\MaryZheng\DockerImages> docker exec -it mysql bash bash-4.4#
In the bash prompt, use the mysql command to login into database as the root user.
login to mysql with the root user
bash-4.4# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 8.0.26 Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Note: the root password can be found in the log.
3.3 Setup MySQL Database and Table
In this step, I will create a demo database with a simple table with six records.
set up table and records
create DATABASE mysqlDemo;
use mysqlDemo;
create table user_tbl(
user_id INT NOT NULL AUTO_INCREMENT,
user_fname VARCHAR(100) NOT NULL,
user_lname VARCHAR(40) NOT NULL,
PRIMARY KEY ( user_id )
);
insert into user_tbl( user_fname , user_lname ) values('Mary','Zheng');
insert into user_tbl( user_fname , user_lname ) values('Tom2','Zheng');
insert into user_tbl( user_fname , user_lname ) values('Jerry2','Cheng');
insert into user_tbl( user_fname , user_lname ) values('MARY','ZHENG');
insert into user_tbl( user_fname , user_lname ) values('TOM2','ZHENG');
insert into user_tbl( user_fname , user_lname ) values('JERRY2','CHENG');4. SQL SELECT DISTINCT Statement Demo
In this step, I will find all distinct user_lname from user_tbl. Here is the query and results:
SELECT DISTINCT
mysql> select distinct user_lname from user_tbl;
mysql> select distinct user_lname from user_tbl; +------------+ | user_lname | +------------+ | Zheng | | Cheng | +------------+ 2 rows in set (0.00 sec)
In this step, I will find all distinct user_lname, user_fname from user_tbl. Here is the query and results:
mysql> select distinct user_lname, user_fname from user_tbl; +------------+------------+ | user_lname | user_fname | +------------+------------+ | Zheng | Mary | | Zheng | Tom2 | | Cheng | Jerry2 | +------------+------------+ 3 rows in set (0.00 sec) mysql>
5. Download the Source Code
Download the source code and scripts to install MySQL and query the data with the SELECT DISTINCT keyword.
Download
You can download the full source code of this example here: SQL SELECT DISTINCT Statement
You can download the full source code of this example here: SQL SELECT DISTINCT Statement
