Types of Joins Overview
SQL JOINs combine rows from two or more tables based on related columns. The type of JOIN determines what happens when no match is found. Understanding the 6 JOIN types - INNER, LEFT, RIGHT, FULL OUTER, SELF, CROSS - is fundamental to working with relational databases.
Join Types Summary
- INNER JOIN - only rows that match in BOTH tables
- LEFT JOIN (LEFT OUTER) - all rows from left table, NULLs for unmatched right
- RIGHT JOIN (RIGHT OUTER) - all rows from right table, NULLs for unmatched left
- FULL OUTER JOIN - all rows from both tables, NULLs where no match
- SELF JOIN - join a table to itself (hierarchies, comparisons)
- CROSS JOIN - every row from left × every row from right (Cartesian product)
- The ON clause specifies the matching condition between tables
Join Diagram: users & orders
-- ecommerce_db relationships
-- users.id ← orders.user_id (one user → many orders)
-- orders.id ← payments.order_id (one order → one payment)
-- products.id ← order_items.product_id (many-to-many via order_items)
-- Venn diagram intuition:
--
-- INNER JOIN LEFT JOIN FULL OUTER JOIN
-- [ A ∩ B ] [ A + (A∩B) ] [ A + B ]
-- matched only all A + matched all from both
-- Basic JOIN syntax
SELECT columns
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.foreign_key_id;
-- JOIN = INNER JOIN (same thing)
-- Always alias table names for readability
SELECT u.name, o.status
FROM users u
JOIN orders o ON u.id = o.user_id;Quick Quiz
Tip
Tip
Practice Types of Joins Overview in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
JOINs combine rows from two tables based on related columns
Common Mistake
Warning
A common mistake with Types of Joins Overview is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Types of Joins Overview from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- SQL JOINs combine rows from two or more tables based on related columns.
- INNER JOIN - only rows that match in BOTH tables
- LEFT JOIN (LEFT OUTER) - all rows from left table, NULLs for unmatched right
- RIGHT JOIN (RIGHT OUTER) - all rows from right table, NULLs for unmatched left