Difference Between Distinct and Group By in PL/SQL

In PL/SQL, knowing the difference between DISTINCT and GROUP BY is important for working with data effectively. Although DISTINCT and GROUP BY might seem similar, they serve different purposes. In this article, we'll explore DISTINCT and GROUP BY Clause with the syntax and various examples along the difference between them for better understanding.

What is the DISTINCT Clause?

The DISTINCT clause is used to retrieve unique values from a specific column or combination of columns in a result set. It eliminates duplicate rows and ensures that only distinct values are returned. DISTINCT can improve query performance by reducing the amount of data that needs to be processed and returned. This can lead to faster query execution times, especially when dealing with large datasets.

Syntax:

SELECT DISTINCT column1, column2
FROM table_name;

Explanation: This query selects unique combinations of values from specified columns in the table.

What is GROUP BY Clause?

The GROUP BY clause is used in conjunction with aggregate functions like SUM, AVG, COUNT, MIN, and MAX to group the result set by one or more columns. It is used to divide the rows returned from the SELECT statement into groups based on the specified columns.

Syntax:

GROUP BY: SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1;

Explanation: This query groups rows based on the values in column1 and applies the aggregate function to column2 for each group.

Examples of DISTINCT and GROUP BY Clause

To understand DISTINCT and GROUP BY Clause in PL/SQL we need a table on which we will perform various operations and queries. Here we will consider a table called employees which contains employee_id, employee_name, salary, age and department_name as Columns.

Query:

-- Create table with new fields
CREATE TABLE employees (
    employee_id INT,
    employee_name VARCHAR(50),
    department_name VARCHAR(50),
    salary DECIMAL(10, 2),
    age INT
);

-- Insert values into the table
INSERT INTO employees (employee_id, employee_name, department_name, salary, age)
SELECT 1, 'John', 'Sales', 62000, 30 FROM DUAL
UNION ALL
SELECT 2, 'Alice', 'IT', 68000, 35 FROM DUAL
UNION ALL
SELECT 3, 'Bob', 'Sales', 65000, 28 FROM DUAL
UNION ALL
SELECT 4, 'Emma', 'HR', 48000, 40 FROM DUAL
UNION ALL
SELECT 5, 'Alice', 'Marketing', 62000, 32 FROM DUAL
UNION ALL
SELECT 6, 'Jane', 'IT', 65000, 29 FROM DUAL;

-- Show table
SELECT * FROM EMPLOYEES;

After Inserting some records into the employees, the table looks:

Example of DISTINCT Clause

Example 1: Find Distinct Department Names

Suppose we have a table named employees with duplicate entries. To fetch unique department names, we use the DISTINCT clause.

Query:

SELECT DISTINCT department_name
FROM employees;

Output:

Explanation: This query retrieves unique department names from the employees table. Each department name appears only once in the output.

Example 2: Find Distinct employee Names

Query:

SELECT DISTINCT employee_name
FROM employees;

Output:

Explanation: This query retrieves unique employee names from the employees table. Each employee name appears only once in the output.

Example of GROUP BY Clause

Example 1: Calculating Sum of Salaries of employees According to Deparment

Calculating the sum of salaries of employees of each department using SUM() function.

Query:

SELECT department_name, SUM(salary) AS total_salary
FROM employees
GROUP BY department_name;

Output:

Explanation: This query calculates the total salary for each department by summing up the salaries of all employees within each department.

Example 2: Calculating Average Age of employees According to Department

Calculating the average age of employees of each department using AVG() function.

Query:

SELECT department_name, AVG(age) AS avg_age
FROM employees
GROUP BY department_name;

Output:

Explanation: This query calculates the average age of employees in each department by averaging the ages of all employees within each department.

Difference Between DISTINCT and GROUP BY

The Differnce between "DISTINCT" and "GROUP BY" is as follows:

Conclusion

Overall, understanding the differences between DISTINCT and GROUP BY clauses in PL/SQL is crucial for efficient data manipulation. DISTINCT is used to retrieve unique values from columns, eliminating duplicates and ensuring each value appears only once. On the other hand, GROUP BY is used to group rows based on specified columns, allowing for the application of aggregate functions to calculate summary information for each group. While DISTINCT is suitable for obtaining unique values, GROUP BY is essential for performing complex analyses and summarizing data based on certain criteria.