What is the difference between UNION and UNION ALL?

The main difference between UNION and UNION ALL in SQL is how they handle duplicate rows:

1. UNION:

   • Combines the results of two or more SELECT queries and removes duplicate rows.
   • Only unique records from all the SELECT statements are returned.

2. UNION ALL:

   • Combines the results of two or more SELECT queries but includes all rows, even duplicates.
   • Returns all records, including duplicates, from the SELECT statements.

Example:

-- Using UNION
SELECT name FROM employees
UNION
SELECT name FROM contractors;

-- Using UNION ALL
SELECT name FROM employees
UNION ALL
SELECT name FROM contractors;

• UNION: Will return a list of unique names from both employees and contractors.
• UNION ALL: Will return all names, including any duplicates that appear in both the employees and contractors tables.

Performance:

• UNION ALL is faster than UNION because it doesn't require the database to check for duplicates.