Example of SELECT DISTINCT vs GROUP BY in MySQL

Let’s assume we have a customers table and an orders table. We are going to use these tables to show how DISTINCT and GROUP BY can be used for different use cases:

Query:

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  city VARCHAR(255) NOT NULL
);

INSERT INTO customers (customer_id, name, city) VALUES
  (1, 'John Doe', 'New York'),
  (2, 'Jane Smith', 'London'),
  (3, 'Mike Brown', 'Paris'),
  (2, 'Jane Smith', 'London');

Output:

Orders Table

Query:

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  product VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO orders (order_id, customer_id, product, price) VALUES
  (1, 1, 'Phone', 100.00),
  (2, 2, 'Laptop', 500.00),
  (3, 1, 'Tablet', 200.00),
  (4, 2, 'Watch', 150.00);

Output:

Example 1: Find distinct customer cities

Here, we want to get names of distinct cities only, so DISTINCT will be the obvious choice here.

Use SELECT DISTINCT to retrieve unique cities where customers reside:

SELECT DISTINCT city
FROM customers;

Output:

Explanation: The SQL query retrieves unique values from the “city” column in the “customers” table. The output provides a list of distinct cities where customers are located, eliminating duplicate entries and showcasing the unique cities in the dataset.

Example 2: Count Orders Per Customer City

Here, we also want to count the number of orders per city. Since an aggregate function(COUNT) is involved here, we will be using GROUP BY.

Use GROUP BY to group customers by city and count their orders:

SELECT city, COUNT(*) AS order_count
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY city;

Output:

Explanation: The SQL query joins the “customers” and “orders” tables on the customer_id, grouping the results by city. It counts the number of orders for each city, providing an output that displays the distinct cities along with the corresponding count of orders placed by customers in each city.

In MySQL, the two most common ways of managing and retrieving unique values are with SELECT and GROUP BY. However, they are used for different reasons. With SELECT, we can get different values from the same column, so we don’t have to worry about duplicates. With GROUP BY, we can aggregate data and group results based on specific columns. However, there are some differences between the two operators. Both can be used to generate the same output. But we need to know the difference for better utilization of resources and time.