Difference between UNION & UNION ALL in SQL?

Both UNION and UNION ALL are used in SQL to combine the results of two or more SELECT statements, but they serve different purposes regarding duplicates in the result set.

creating sample tables:

Table Definitions:

1. CREATE TABLE table1 and CREATE TABLE table2

• These SQL commands create two tables named table1 and table2.
• Each table has one column named column1, which stores strings (i.e., text values).

2. column1 VARCHAR(50):

• column1: This is the name of the column in both tables.
• VARCHAR(50): The VARCHAR data type is a "variable character string." It allows you to store text up to 50 characters long. The size (50) limits the number of characters stored in this column.

Inserting Sample Data into Both Tables:

'Apple', 'Banana', 'Cherry', 'Date', 'Elderberry': These are the data elements (text strings) being inserted into column1 of the respective tables. Each value represents a row in the table.

1. UNION:

• Functionality: Removes duplicate rows from the result set.
• Returns: Only distinct rows from the combined queries.

Example (SQL query):

Output:

In this example, the query combines results from table1 and table2, returning only unique values of column1.

2. UNION ALL:

• Functionality: Does not remove duplicate rows; it includes all rows from the combined queries.
• Returns: All rows, even if they are duplicates.

Example (SQL query):

Output:

In this example, the query returns all values of column1 from both tables, including duplicates.

Summary:

UNION: Use when you want to eliminate duplicate rows from the result set.

UNION ALL: Use when you want to include all rows, including duplicates.

Note: UNION is generally more resource-intensive because it involves sorting and removing duplicates. If you know there are no duplicates or want to keep them, UNION ALL can be more efficient. Understanding the distinction between UNION and UNION ALL can greatly enhance your SQL querying skills, especially when working with large datasets!