Let's Work with Multiple Tables

Hey there! In this tutorial, we'll learn how to get and manage data from multiple tables using SQL joins. Knowing how to work with multiple tables is key when dealing with complex databases where data is spread across different related tables.

Table of Contents

1. Understanding Joins in SQL
2. What's an Inner Join?
3. What's a Left Join?
4. What's a Right Join?
5. What's a Full Outer Join?
6. What's a Cross Join?

Understanding Joins in SQL

Joins let you mix rows from two or more tables in SQL based on related columns. This way, you can write queries that pull data from multiple tables as if everything was in one place.

What's an Inner Join?

A Inner Join gives you records where both tables have matching values.

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Example: Let's say you have two tables: Orders and Customers. To get orders with customer names, you'd use:

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

What's a Left Join?

A Left Join brings in all records from the left table and the matching ones from the right table. If there's no match, you'll see NULL on the right side.

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Example: To get all customers and their orders (if they have any), you'd use:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

What's a Right Join?

A Right Join brings in all records from the right table and the matching ones from the left table. If there's no match, you'll see NULL on the left side.

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Example: To get all orders and the customer names (if available), you'd use:

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
RIGHT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

What's a Full Outer Join?

A Full Outer Join gives you all records when there's a match in either the left or right table. If there's no match, you'll see NULL on the side without a match.

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

Note: Not all SQL versions support FULL OUTER JOIN. For instance, MySQL doesn't support it directly.

What's a Cross Join?

A Cross Join gives you the Cartesian product of rows from the tables in the join. It combines each row of the first table with every row of the second table.

SELECT columns
FROM table1
CROSS JOIN table2;

Example: To combine all products with all categories, you'd use:

SELECT Products.ProductName, Categories.CategoryName
FROM Products
CROSS JOIN Categories;