#1 Spot for Defeating Online Exams

difference-between-union-and-union-all-in-sql

The primary distinction between UNION and UNION ALL lies in their treatment of duplicate entries. 

The approaches for handling duplicates in UNION and UNION ALL are different. UNION amalgamates two or more select queries while eliminating duplicate entries, whereas UNION ALL retains all records, including repetitions, by simply appending them together. 

Contents Overview:

What is UNION in SQL?

The UNION in SQL is utilized to merge two or more SELECT statement results while filtering out any duplicates from the tables in the output set. 

Syntax:

SELECT column1, column2, column3, ...
FROM table1
WHERE condition
UNION
SELECT column1, column2, column3, ...
FROM table2
WHERE condition;

Example:

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(50)
);
CREATE TABLE suppliers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(50)
);
INSERT INTO customers (id, name, city) VALUES
(1, 'Bahadhur', 'Kolkata'),
(2, 'Hema', 'Tamil Nadu'),
(3, 'Chahar', 'Delhi'),
(4, 'Dan', 'Kerala');
INSERT INTO suppliers (id, name, city) VALUES
(1, 'Supplier A', 'Kolkata'),
(2, 'Supplier B', 'Tamil Nadu'),
(3, 'Supplier C', 'Delhi'),
(4, 'Supplier D', 'Kerala');
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

Output:

What is UNION in SQL

Explanation: The UNION operation merges two datasets into one. In this case, the UNION command consolidated the city data from both tables, eliminated duplicates, and presented distinct entries. 

What is UNION ALL in SQL?

UNION ALL functions similarly to UNION; it amalgamates all result data from SELECT statements but will retain all duplicates from the data output. It aggregates both unique and duplicate entries from the datasets.

Syntax:

SELECT column1, column2, column3, ...
FROM table1
WHERE condition

UNION ALL

SELECT column1, column2, column3, ...
FROM table2
WHERE condition;
CREATE TABLE sales_2023 (
    order_id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10,2)
);

Example:

CREATE TABLE sales_2024 (
    order_id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10,2)
);
-- Insert sales data for 2023
INSERT INTO sales_2023 (order_id, customer_id, amount) VALUES
(101, 1, 150.75),
(102, 2, 220.50),
(103, 3, 340.00),
(104, 4, 180.25);
-- Insert sales data for 2024
INSERT INTO sales_2024 (order_id, customer_id, amount) VALUES
(201, 2, 200.00),
(202, 3, 400.75),
(203, 5, 120.50),
(204, 6, 310.90);
SELECT order_id, customer_id, amount, '2023' AS year FROM sales_2023
UNION ALL
SELECT order_id, customer_id, amount, '2024' AS year FROM sales_2024;

Output:

What is UNION ALL in SQL

Explanation: The UNION ALL combines all information from customers and sales, including duplicates. 

Key Differences Between UNION and UNION ALL

UNION UNION ALL
Eliminates all duplicates from the dataset. Does not eliminate duplicates from the dataset.
Slower in execution due to the removal of duplicates. Quicker in execution since it skips duplicate handling.
Requires higher memory usage for sorting. Does not require significant memory resources. 
Produces unique data entries Includes all values, even duplicates. 
Key Difference Between UNION and UNION ALL

Guidelines for UNION and UNION ALL in MySQL

  1. Each SELECT statement must return an equivalent number of columns
  2. Data types of each column should match correspondingly.
  3. UNION will filter out duplicate records.
  4. UNION ALL will incorporate all rows, including duplicates.
  5. ORDER BY should follow after the SELECT command.
  6. UNION typically performs slower as it needs to address duplicates.
  7. UNION ALL is faster as it avoids duplicate management.
  8. NULL values are treated as regular values in both UNION and UNION ALL.

Practical Examples of UNION and UNION ALL

1. Combining Job Listings with Freelancers Using UNION:

The UNION function will integrate job positions from various sources, eliminate duplicates, and yield unique results. 

Example:

CREATE TABLE full_time_jobs (
    id INT PRIMARY KEY,
    job_title```sql
 VARCHAR(100),
    organization VARCHAR(100),
    place VARCHAR(50)
);
CREATE TABLE freelance_positions (
    id INT PRIMARY KEY,
    title VARCHAR(100),
    customer VARCHAR(100),
    is_remote BOOLEAN
);

-- Insert full-time job postings
INSERT INTO full_time_jobs (id, title, organization, place) VALUES
(1, 'Software Developer', 'ERV', 'Delhi'),
(2, 'Data Scientist', 'PQR', 'Srinagar'),
(3, 'Marketing Head', 'ABC', 'Lucknow'),
(4, 'Software Developer', 'XYZ', 'Hyderabad');

-- Insert freelance job postings
INSERT INTO freelance_positions (id, title, customer, is_remote) VALUES
(1, 'Software Developer', 'Client A', TRUE),
(2, 'Graphic Artist', 'Client B', TRUE),
(3, 'Data Scientist', 'Client C', TRUE),
(4, 'SEO Expert', 'Client D', TRUE);

SELECT title FROM full_time_jobs
UNION
SELECT title FROM freelance_positions;

Output:

Combining Job Titles With Freelancers Using UNION

Explanation: The UNION merged job titles with freelance roles and returned distinct values from the tables. 

2. Hospital Records With UNION ALL: 

The diagnoses of patients from the general ward and the ICU present in the hospital database, the UNION ALL, will yield the results of amalgamating both diagnoses from the general ward and ICU. 

Example:

CREATE TABLE general_ward (
    id INT PRIMARY KEY,
    patient_name VARCHAR(100),
    condition VARCHAR(100)
);
CREATE TABLE icu_ward (
    id INT PRIMARY KEY,
    patient_name VARCHAR(100),
    condition VARCHAR(100)
);

-- Insert patient records in the General Ward
INSERT INTO general_ward (id, patient_name, condition) VALUES
(1, 'Alex', 'Pneumonia'),
(2, 'Baskar', 'Diabetes'),
(3, 'Sneha', 'Hypertension'),
(4, 'Shekhar', 'Pneumonia');  -- Duplicate condition

-- Insert patient records in ICU
INSERT INTO icu_ward (id, patient_name, condition) VALUES
(1, 'Eric', 'COVID-19'),
(2, 'Bishnoi', 'Pneumonia'),  -- Duplicate condition
(3, 'Govind', 'Diabetes'),    -- Duplicate condition
(4, 'Hari', 'Cardiac Arrest');

SELECT condition FROM general_ward
UNION ALL
SELECT condition FROM icu_ward;

Output:

Hospital Records With UNION ALL

Explanation: This outputs the diagnoses of patients in general wards and ICU, inclusive of duplicate entries.

Recommended Practices for Using UNION and UNION ALL

  • Utilize UNION when eliminating duplicate records is necessary for preserving data integrity.
  • Choose UNION ALL when duplicates are permissible, as it enhances performance by avoiding the need for sorting and filtering.
  • Ensure uniformity in the number of columns and compatible data types across all SELECT statements.
  • Implement filters such as WHERE clauses in individual SELECT statements to narrow down the dataset prior to employing UNION or UNION ALL.
  • Specify column names explicitly in the final query to enhance clarity and maintainability.

Performance Considerations: UNION vs UNION ALL

  • UNION necessitates further processing to sort and eliminate duplicates, which can hinder performance, particularly with extensive datasets.
  • UNION ALL generally performs better as it directly consolidates results without filtering for duplicates.
  • In scenarios with high data volume, UNION ALL is typically favored unless uniqueness is crucial.
  • Utilizing proper indexing and limiting returned rows with clauses like LIMIT or TOP can further optimize performance.
  • Examine query execution plans to detect bottlenecks and make enhancements as necessary.

Applications of UNION and UNION ALL

UNION:

  • UNION can be applied to merge customer databases from various branches without any duplications.
  • It also assists in combining employee data from different divisions.
  • It can generate sales reports containing transaction histories with no duplicates. 

UNION ALL:

  • It consolidates all server logs, including duplicate event records.
  • It compiles global monthly sales while retaining duplication of all events. 

Conclusion

The UNION and UNION ALL are SQL commands that combine two SELECT queries. The UNION command yields unique entries after eliminating duplicates, whereas UNION ALL maintains all entries from the tables, including duplications. The fundamental distinction between the two lies in how duplicates are treated. UNION preserves code redundancy but incurs a longer processing time compared to UNION ALL due to sorting. Essentially, UNION ALL operates more quickly as it returns all entries. 

FAQs

The post Difference between UNION and UNION ALL in SQL appeared first on Intellipaat Blog.

“`

Staff SEO Expert

Leave a Reply

Your email address will not be published. Required fields are marked *

Share This