JOIN vs. UNION in SQL: A Simplified Explanation

JOIN vs. UNION in SQL: A Simplified Explanation

JOIN and UNION are two fundamental SQL operations used to combine rows from multiple tables. However, they serve distinct purposes.

JOIN

• Purpose: Combines rows from two or more tables based on a related column.
• How it works: Matches rows from different tables that share a common value in a specified column.

Example:

SELECT customers.customer_id, orders.order_id
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;   

This query combines customer information with their orders, showing only customers who have placed orders.

UNION

• Purpose: Combines the result sets of two or more SELECT statements.
• How it works: Appends the results of multiple queries into a single result set.
• Rules:
  • The number and types of columns in each SELECT statement must be identical.
  • Duplicate rows are automatically removed.

SELECT product_name FROM products
UNION
SELECT product_name FROM discontinued_products;

This query combines the list of all products with the list of discontinued products, providing a comprehensive list of all products.

In summary:

• JOIN is used to relate data from different tables based on a common column.

Understanding JOIN and UNION with Examples

JOIN: Combining Data from Multiple Tables

Example: INNER JOIN

SELECT customers.customer_id, orders.order_id
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;   

This query combines the customers and orders tables based on the customer_id column. It will return only the rows where there's a matching customer_id in both tables.

SELECT customers.customer_id, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;   

This query returns all rows from the customers table, even if there's no matching row in the orders table. If there's no match, the corresponding columns in the orders table will be NULL.  

UNION: Combining Result Sets

Example: UNION

SELECT product_name FROM products
UNION
SELECT product_name FROM discontinued_products;

This query combines the results of two SELECT statements. It will return a list of all product names, including those that are discontinued. Duplicate product names will be automatically removed.

SELECT product_name FROM products
UNION ALL
SELECT product_name FROM discontinued_products;

This query is similar to UNION, but it will retain duplicate product names.

Key Differences:

• JOIN: Combines data from multiple tables based on a common column.
• UNION: Combines the results of multiple SELECT statements.

When to use which:

• JOIN: When you need to combine data from related tables, such as customers and their orders.
• UNION: When you need to combine the results of multiple queries, such as finding all products, regardless of whether they are discontinued.

Alternative Methods for JOIN and UNION in SQL

While JOIN and UNION are fundamental operations in SQL, there are alternative approaches that can be used in certain scenarios:

Subqueries

• Purpose: Can replace JOINs in some cases, especially when the joined table is used only once.
• Example:

  SELECT customers.customer_id, orders.order_id
  FROM customers
  WHERE customers.customer_id IN (SELECT order_id FROM orders);
  

  This subquery is equivalent to an INNER JOIN between customers and orders.

Common Table Expressions (CTEs)

• Purpose: Provide a temporary result set that can be referenced multiple times within a single query.
• Example:

  WITH high_sales AS (
      SELECT product_id, SUM(quantity) AS total_sales
      FROM sales
      GROUP BY product_id
      HAVING SUM(quantity) > 1000
  )
  SELECT products.product_name, high_sales.total_sales
  FROM products
  INNER JOIN high_sales ON products.product_id = high_sales.product_id;
  

  This CTE calculates high-selling products and then joins the result with the products table.

Views

• Purpose: Define a virtual table based on a SQL query.
• Example:

  CREATE VIEW high_sales_products AS
  SELECT product_id, SUM(quantity) AS total_sales
  FROM sales
  GROUP BY product_id
  HAVING SUM(quantity) > 1000;
  

  Once created, the high_sales_products view can be used like any other table.

Window Functions

• Purpose: Perform calculations over a set of rows, often within a partition.
• Example:

  SELECT product_id, SUM(quantity) OVER (PARTITION BY product_id) AS total_sales
  FROM sales;
  

  This query calculates the total sales for each product using a window function.

Choosing the right method:

• Subqueries: Use when the joined table is referenced only once or when you need to filter the outer query based on the inner query.
• CTEs: Use when you need to reference a temporary result set multiple times within a single query.
• Views: Use when you frequently need to execute the same query or when you want to abstract complexity from your main queries.
• Window functions: Use when you need to perform calculations over a set of rows, such as calculating running totals or ranking.