Day 27:Joins and Unions

Day 2: Joins and Unions

Overview

On Day 2, we will learn about the different types of joins, how to write joins to combine data from multiple tables, and how to use unions to combine data from multiple queries.

Objectives

By the end of this lesson, you will be able to:

  • Understand the different types of joins (inner, outer, left, right)
  • Write joins to combine data from multiple tables
  • Use unions to combine data from multiple queries

Lesson

1. Understanding different types of joins

  • Inner joins: returns only the rows that have matching values in both tables
  • Outer joins: returns all the rows from one table and the matching rows from the other table, and NULL values where there is no match
    • Left outer join: returns all the rows from the left table and the matching rows from the right table, and NULL values where there is no match
    • Right outer join: returns all the rows from the right table and the matching rows from the left table, and NULL values where there is no match

2. Writing joins to combine data from multiple tables

  • Syntax: SELECT columns FROM table1 JOIN table2 ON table1.column = table2.column
  • Examples:
    • Inner join: SELECT customers.name, orders.order_date FROM customers JOIN orders ON customers.id = orders.customer_id
    • Left outer join: SELECT customers.name, orders.order_date FROM customers LEFT JOIN orders ON customers.id = orders.customer_id
    • Right outer join: SELECT customers.name, orders.order_date FROM customers RIGHT JOIN orders ON customers.id = orders.customer_id

3. Using unions to combine data from multiple queries

  • Syntax: SELECT columns FROM table1 UNION SELECT columns FROM table2
  • Examples:
    • Combining data from two tables: SELECT name FROM customers UNION SELECT name FROM suppliers
    • Combining data from two queries: SELECT name FROM customers WHERE age > 30 UNION SELECT name FROM suppliers WHERE city = 'New York'

Exercise

  1. Write a query to return the names of all the customers who have placed an order.
  2. Write a query to return the names of all the customers and the order dates of their orders (if they have any).
  3. Write a query to return the names of all the customers, the order dates of their orders (if they have any), and the names of the products they have ordered (if they have any).