SQL JOINs

SQL JOINs allow you to combine rows from two or more tables based on a related column. JOINs are essential for relational databases because they let you access related data stored in different tables efficiently.

📌 Common Types of JOINs

• INNER JOIN: Returns rows when there is a match in both tables.
• LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and matched rows from the right table; NULL if no match.
• RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table and matched rows from the left table; NULL if no match.
• FULL OUTER JOIN: Returns rows when there is a match in one of the tables; NULL for missing matches.

📊 Example Tables

Users Table:

id | name
---+-------
1  | Alice
2  | Bob
3  | Charlie
4  | Diana

Orders Table:

order_id | user_id | product   | amount
---------+---------+----------+-------
1        | 1       | Laptop    | 1200
2        | 2       | Phone     | 800
3        | 1       | Keyboard  | 100
4        | 3       | Monitor   | 300

🔍 Example 1: INNER JOIN

Get all users with their orders:

SELECT users.name, orders.product, orders.amount
FROM users
INNER JOIN orders
ON users.id = orders.user_id;

🔍 Example 2: LEFT JOIN

Get all users, including those without orders:

SELECT users.name, orders.product, orders.amount
FROM users
LEFT JOIN orders
ON users.id = orders.user_id;

🔍 Example 3: RIGHT JOIN

Get all orders, including any orders without matching users (if possible):

SELECT users.name, orders.product, orders.amount
FROM users
RIGHT JOIN orders
ON users.id = orders.user_id;

🔍 Example 4: FULL OUTER JOIN

Get all users and all orders, matching where possible:

SELECT users.name, orders.product, orders.amount
FROM users
FULL OUTER JOIN orders
ON users.id = orders.user_id;

⚡ Best Practices

• Always join on indexed columns for better performance.
• Use INNER JOIN when you only need matching data.
• LEFT/RIGHT JOIN is useful when you need all rows from one table.
• Use table aliases to make queries easier to read.

📝 Summary

SQL JOINs are powerful tools to combine related data from multiple tables. Choosing the correct type of JOIN ensures accurate results and efficient queries. INNER JOIN returns matching rows, LEFT/RIGHT JOIN returns all rows from one table, and FULL OUTER JOIN returns all rows from both tables.

🚀 Next Steps

Next, we will learn about SQL UNION and UNION ALL, which allow combining results from multiple queries into a single result set.