The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Joins and Unions

Next ►
►

In this section, we will explore two fundamental SQL operations: Joins and Unions. These operations are essential for combining data from multiple tables, which is a common requirement in data analysis and reporting.

What are Joins?

Joins are used to combine rows from two or more tables based on a related column between them. There are several types of joins, each serving a different purpose:

  1. Inner Join: Returns only the rows that have matching values in both tables.
  2. Left Join (or Left Outer Join): Returns all rows from the left table and the matched rows from the right table. If no match is found, NULL values are returned for columns from the right table.
  3. Right Join (or Right Outer Join): Returns all rows from the right table and the matched rows from the left table. If no match is found, NULL values are returned for columns from the left table.
  4. Full Join (or Full Outer Join): Returns all rows when there is a match in either left or right table. Rows without a match in one of the tables will have NULL values for columns from that table.
  5. Cross Join: Returns the Cartesian product of the two tables, i.e., all possible combinations of rows.

Inner Join Example

SELECT 
    a.id, 
    a.name, 
    b.order_id, 
    b.amount
FROM 
    customers a
INNER JOIN 
    orders b
ON 
    a.id = b.customer_id;

Explanation:

  • customers and orders are the two tables.
  • The INNER JOIN keyword is used to combine rows from both tables.
  • The ON clause specifies the condition for the join, which is a.id = b.customer_id.

Left Join Example

SELECT 
    a.id, 
    a.name, 
    b.order_id, 
    b.amount
FROM 
    customers a
LEFT JOIN 
    orders b
ON 
    a.id = b.customer_id;

Explanation:

  • This query returns all customers, including those who have not placed any orders. For customers without orders, the order_id and amount columns will contain NULL values.

Right Join Example

SELECT 
    a.id, 
    a.name, 
    b.order_id, 
    b.amount
FROM 
    customers a
RIGHT JOIN 
    orders b
ON 
    a.id = b.customer_id;

Explanation:

  • This query returns all orders, including those that do not have a corresponding customer. For such orders, the id and name columns will contain NULL values.

Full Join Example

SELECT 
    a.id, 
    a.name, 
    b.order_id, 
    b.amount
FROM 
    customers a
FULL JOIN 
    orders b
ON 
    a.id = b.customer_id;

Explanation:

  • This query returns all customers and all orders. Rows without a match in one of the tables will have NULL values for columns from that table.

Cross Join Example

SELECT 
    a.id, 
    a.name, 
    b.order_id, 
    b.amount
FROM 
    customers a
CROSS JOIN 
    orders b;

Explanation:

  • This query returns the Cartesian product of the customers and orders tables, i.e., every possible combination of rows from both tables.

What are Unions?

Unions are used to combine the results of two or more SELECT queries into a single result set. The queries must have the same number of columns in the result sets with similar data types.

Union Example

SELECT 
    id, 
    name 
FROM 
    customers
UNION
SELECT 
    id, 
    name 
FROM 
    suppliers;

Explanation:

  • This query combines the results from the customers and suppliers tables.
  • The UNION operator removes duplicate rows from the result set.

Union All Example

SELECT 
    id, 
    name 
FROM 
    customers
UNION ALL
SELECT 
    id, 
    name 
FROM 
    suppliers;

Explanation:

  • This query combines the results from the customers and suppliers tables.
  • The UNION ALL operator includes all rows, including duplicates.

Practical Exercises

Exercise 1: Inner Join

Task: Write a query to find all orders along with the customer names who placed them.

SELECT 
    c.name, 
    o.order_id, 
    o.amount
FROM 
    customers c
INNER JOIN 
    orders o
ON 
    c.id = o.customer_id;

Exercise 2: Left Join

Task: Write a query to list all customers and their orders, including customers who have not placed any orders.

SELECT 
    c.name, 
    o.order_id, 
    o.amount
FROM 
    customers c
LEFT JOIN 
    orders o
ON 
    c.id = o.customer_id;

Exercise 3: Union

Task: Write a query to combine the names of all customers and suppliers into a single list.

SELECT 
    name 
FROM 
    customers
UNION
SELECT 
    name 
FROM 
    suppliers;

Common Mistakes and Tips

  • Mismatched Columns in Unions: Ensure that the number of columns and their data types match in all SELECT statements used in a UNION.
  • NULL Handling in Joins: Be aware of how NULL values are handled in different types of joins, especially in LEFT and RIGHT joins.
  • Cartesian Product in Cross Joins: Use CROSS JOIN with caution as it can produce a very large result set.

Conclusion

In this section, we covered the basics of Joins and Unions in SQL, which are essential for combining data from multiple tables. We explored different types of joins and how to use the UNION operator to merge result sets. Understanding these concepts is crucial for effective data analysis and manipulation in BigQuery. In the next section, we will delve into Subqueries, which allow for more complex and nested queries.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved