MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

Distinct Clause
MySQL DISTINCT clause
The DISTINCT clause in MySQL is used with a SELECT statement to return the distinct values (unique values) from a single or multiple of columns in a table. It ignores all the duplicates values present in the particular column(s) and returns only the distinct values.
We can use this clause in various scenarios, such as identifying unique customer names, unique customer id's, etc. It can be combined with other clauses such as WHERE, ORDER BY, and GROUP BY to filter the data further.
Syntax
Following is the syntax of the DISTINCT clause in MySQL 
SELECT DISTINCT column1, column2, ..., columnN
FROM table_name
WHERE conditions // optional
Where,
(column1, column2,...,columnN) are the columns from which we want the distinct (unique) values.
table_name is the name of the table from which we want to select data.
WHERE conditions is optional. These are used to filter the data.
Example
Firstly, let us create a create a table named CUSTOMERS using the following INSERT query 
CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);
The following INSERT INTO statement adds 7 records into the above-created table 
INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES
(1, 'Ramesh', 32, 'Hyderabad', NULL),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Hyderabad', 2000.00),
(4, 'Chaital', 25, 'Mumbai', NULL),
(5, 'Hardik', 27, 'Vishakapatnam', 8500.00),
(6, 'Komal', 22, 'Vishakapatnam', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);
Execute the below query to display all the inserted records in the CUSTOMERS table 
SELECT * FROM CUSTOMERS;
Following is the CUSTOMERS table 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Hyderabad
NULL
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Hyderabad
2000.00
4
Chaitali
25
Mumbai
NULL
5
Hardik
27
Vishakapatnam
8500.00
6
Komal
22
Vishakapatnam
4500.00
7
Muffy
24
Indore
10000.00
Now, let us retrieve the ADDRESS column from CUSTOMERS table without using the DISTINCT clause.
SELECT ADDRESS FROM CUSTOMERS;
Duplicate values are not ignored in the ADDRESS column.
ADDRESS
Hyderabad
Delhi
Hyderabad
Mumbai
Vishakapatnam
Vishakapatnam
Indore
Here, we are using the DISTINCT clause on the ADDRESS column 
SELECT DISTINCT ADDRESS FROM CUSTOMERS;
Output
As we can see in the output below, duplicate values are ignored in the ADDRESS column.
ADDRESS
Hyderabad
Delhi
Mumbai
Vishakapatnam
Indore
DISTINCT Clause with COUNT() Function
The MySQL count() function allows us to count the number of distinct values present in one or more columns of a table. Let us understand with the example below
Example
In the following query, we are using the MySQL COUNT() function to count the DISTINCT records in ADDRESS column of CUSTOMERS table 
SELECT COUNT(DISTINCT ADDRESS) FROM CUSTOMERS;
Output
There are 5 distinct records present in the ADDRESS column.
COUNT(DISTINCT ADDRESS)
5
Example
In this query, we are retrieving unique SALARY records from the CUSTOMERS table where the ADDRESS is "Hyderabad".
SELECT DISTINCT SALARY FROM CUSTOMERS WHERE ADDRESS = "HYDERABAD";
Output
The output for the program above is produced as given below 
SALARY
NULL
2000.00
DISTINCT on Multiple Columns
We can use the MySQL DISTINCT keyword on multiple columns of a table to return all the unique combinations of values across those columns, i.e. removing redundant records in a table.
Example
In the following query, we are retrieving the distinct combinations of ADDRESS and SALARY columns from the CUSTOMERS table and orders the result set by the ADDRESS column in ascending order.
SELECT DISTINCT ADDRESS, SALARY FROM CUSTOMERS ORDER BY ADDRESS;
Output
As we can see in the output below, the duplicate values "Hyderabad" and "Vishakapatnam" appears twice in the result set because each combination of Hyderabad and Vishakapatnam with SALARY is unique.
ADDRESS
SALARY
Delhi
1500.00
Hyderabad
NULL
Hyderabad
2000.00
Indore
10000.00
Mumbai
NULL
Vishakapatnam
4500.00
Vishakapatnam
8500.00
DISTINCT with NULL values
If there are NULL values present in a specific column, the MySQL DISTINCT will treat them as unique values and includes them in the result set.
Example
Here, we are returning the distinct salary of the customers using the following query 
SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY;
Output
On executing the given program, the output is displayed as follows 
SALARY
NULL
1500.00
2000.00
4500.00
8500.00
10000.00
Distinct Clause Using a Client Program
In addition to fetch distinct records from a table with a MySQL query, you can also use a client program to perform the DISTINCT operation.
Syntax
Following are the syntaxes of this operation in various programming languages 
PHPNodeJSJavaPython
To fetch distinct records from a MySQL table through PHP program, we need to execute SELECT statement with DISTINCT clause using the mysqli function query() as follows 
$sql = "SELECT DISTINCT EXPRESSION FROM TABLE_NAME [WHERE CONDITION]";
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.');
$sql = 'SELECT DISTINCT tutorial_title FROM tutorials_tbl where tutorial_id > 2';
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   while($row = $result->fetch_assoc()) {
      printf("Title: %s ",  
         $row["tutorial_title"], "\n");               
   }
} else {
   printf('No record found.');
}
mysqli_free_result($result);
$mysqli->close();
Output
The output obtained is as follows 
Title: JAVA Tutorial Title: Learn PHP Title: Learn MySQL        
﻿
﻿
﻿
﻿
﻿
﻿

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Hyderabad	NULL
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Hyderabad	2000.00
4	Chaitali	25	Mumbai	NULL
5	Hardik	27	Vishakapatnam	8500.00
6	Komal	22	Vishakapatnam	4500.00
7	Muffy	24	Indore	10000.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Distinct Clause

MySQL DISTINCT clause

Syntax

Example

Output

DISTINCT Clause with COUNT() Function

Example

Output

Example

Output

DISTINCT on Multiple Columns

Example

Output

DISTINCT with NULL values

Example

Output

Distinct Clause Using a Client Program

Syntax

Example

Output