Interview #32: SQL: What is the difference between JOIN and UNION?

In SQL, both JOIN and UNION are used to combine data from multiple tables, but they do so in different ways and serve different purposes. Here's an in-depth explanation of the differences, including their definitions, use cases, and working principles.

Disclaimer: For QA-Testing Jobs, WhatsApp us @ 91-6232667387

1. JOIN

Definition: The JOIN clause is used to combine rows from two or more tables based on a related column (or columns) between them. It merges data horizontally by adding columns from the second table to the result set of the first table based on a condition.

Types of JOINs

• INNER JOIN: Returns only matching rows from both tables.
• LEFT (OUTER) JOIN: Returns all rows from the left table and matching rows from the right table. If no match exists, NULL values are filled for the right table's columns.
• RIGHT (OUTER) JOIN: Returns all rows from the right table and matching rows from the left table. If no match exists, NULL values are filled for the left table's columns.
• FULL (OUTER) JOIN: Returns all rows from both tables. Rows with no matches are filled with NULLs.
• CROSS JOIN: Produces a Cartesian product of two tables, returning all combinations of rows.

Working of JOIN

• JOIN works at the row level. It evaluates rows from one table and matches them with rows from another table based on a given condition (typically a foreign key or other relational field).

Example

Let’s say we have two tables:

Table: Employees

Employees

Table: Departments

Departments

INNER JOIN Example:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Output:

Inner Join Output

2. UNION

Definition: The UNION operator is used to combine the result sets of two or more SELECT queries. It merges data vertically by appending rows from one result set to another.

Types of UNION

• UNION: Removes duplicate rows from the final result.
• UNION ALL: Includes all rows, even if duplicates exist.

Working of UNION

• UNION works at the column level. The columns in the SELECT queries must have the same number, data types, and order, as the UNION combines rows by aligning corresponding columns from the queries.

Example

Consider two tables:

Table: USA_Customers

USA Customers

Table: UK_Customers

UK Customers

UNION Example:

SELECT Name, Country
FROM USA_Customers
UNION
SELECT Name, Country
FROM UK_Customers;

Output:

UNION Output

Key Differences Between JOIN and UNION

When to Use JOIN and UNION?

Use JOIN When:

• You need to combine related data from two or more tables with a common key or relationship.
• You want to include additional attributes (columns) in the result set.

Use UNION When:

• You need to combine the results of similar SELECT queries into a single dataset.
• You want to merge results from unrelated tables or queries with the same structure.

---

Conclusion

In summary, JOIN is used to combine related tables into a single dataset by matching rows based on conditions, while UNION is used to combine the results of multiple queries into a unified dataset. Both serve distinct purposes and are fundamental tools in SQL for managing and analyzing relational data. Understanding their differences and appropriate use cases is essential for writing efficient and meaningful SQL queries.

Previous: Interview #31: Selenium-Java: How to perform data-driven testing with an Excel file?