INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN

# CHAPTER 14

INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN #

1. Introduction #

2. Learning Objectives #

• Understand the concept of Table Joins via Foreign Keys.

• Use INNER JOIN to fetch strictly matching data.

• Use LEFT JOIN to fetch matching data plus all remaining parent rows.

• Understand the rarely used RIGHT JOIN and FULL OUTER JOIN.

• Use Table Aliases to write cleaner, faster queries.

3. The INNER JOIN (The Strict Match) #

-- Let's stitch users and orders together!
SELECT users.first_name, orders.total_price 
FROM users
INNER JOIN orders 
    ON users.id = orders.user_id; -- This is the mathematical bridge!

How it works: PostgreSQL looks at the FROM table (users). It then grabs the orders table. The ON clause tells PostgreSQL exactly how to snap the two tables together (by matching the Primary Key to the Foreign Key).

4. Table Aliases (Writing Cleaner Code) #

SELECT u.first_name, o.total_price 
FROM users AS u
INNER JOIN orders AS o 
    ON u.id = o.user_id;

5. The LEFT JOIN (The Inclusive Match) #

A LEFT JOIN returns ALL rows from the Left table (the FROM table), regardless of whether they have a match in the Right table. If there is no match, the Right table columns are filled with NULL.

SELECT u.first_name, o.total_price 
FROM users u
LEFT JOIN orders o 
    ON u.id = o.user_id;

*(Sarah, who has no orders, will now appear in the list, and her totalprice will show as NULL!)*

SELECT 
    u.first_name, 
    p.product_name, 
    oi.quantity
FROM users u
-- Link Users to Orders
INNER JOIN orders o 
    ON u.id = o.user_id
-- Link Orders to the Pivot Table
INNER JOIN order_items oi 
    ON o.id = oi.order_id
-- Link the Pivot Table to Products!
INNER JOIN products p 
    ON oi.product_id = p.id;

9. Common Mistakes #

• Ambiguous Column Names: If both the users table and the products table have a column named id, and you just type SELECT id, PostgreSQL will crash with an "Ambiguous Column" error. You MUST specify which table it belongs to: SELECT u.id.

• Forgetting the ON clause: If you write a JOIN but forget the ON condition, PostgreSQL doesn't know how to link them. It will multiply every row by every row (a Cross Join), resulting in millions of garbage results.

10. Best Practices #

• Standardize on LEFT JOIN: When building reports where you need a base list of entities (like "All Users" or "All Products") regardless of their activity, always structure your query to make that base list the FROM table, and LEFT JOIN the activity tables to it.

11. Exercises #

1. 1. Write a query using INNER JOIN to fetch the departmentname and the employeename for all active employees.

1. 2. What is the fundamental difference in the output between an INNER JOIN and a LEFT JOIN?

12. SQL Challenges #

SELECT d.dept_name, e.first_name 
FROM departments d
LEFT JOIN employees e 
    ON d.dept_id = e.department_id;

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain the difference between an INNER JOIN and a FULL OUTER JOIN. In what business scenario would you explicitly need a FULL OUTER JOIN?

• Q: If a query joining three tables is failing with an "Ambiguous Column Reference" error, what does that mean and how do you fix it?

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #