sql

SQL SELECT DISTINCT Statement

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

sql distinct

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

Mary Zheng

Mary has graduated from Mechanical Engineering department at ShangHai JiaoTong University. She also holds a Master degree in Computer Science from Webster University. During her studies she has been involved with a large number of projects ranging from programming and software engineering. She works as a senior Software Engineer in the telecommunications sector where she acts as a leader and works with others to design, implement, and monitor the software solution.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button