What Is a SQL JOIN?
Relational databases store data across multiple tables. A JOIN is a SQL operation that combines rows from two or more tables based on a related condition — typically a foreign key to primary key relationship — producing a single result set.
The relationship lines you draw in an ERD translate directly into JOIN conditions in your queries. Understanding your ERD means writing JOIN queries naturally.
Sample Tables
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT REFERENCES users(id),
amount DECIMAL(10,2)
);
-- Sample data
INSERT INTO users VALUES (1,'Alice'), (2,'Bob'), (3,'Carol');
INSERT INTO orders VALUES (1,1,50.00),(2,1,30.00),(3,2,80.00),(4,NULL,20.00);
-- Order 4 has NULL user_id; Carol has no orders
INNER JOIN: Intersection
Returns only rows that have a match in both tables.
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
| name | amount |
|---|---|
| Alice | 50.00 |
| Alice | 30.00 |
| Bob | 80.00 |
Carol is excluded (no orders). Order 4 (user_id = NULL) is also excluded.
LEFT JOIN: Keep All Left Rows
Returns all rows from the left table (users), filling with NULL when there is no match in the right table (orders).
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
| name | amount |
|---|---|
| Alice | 50.00 |
| Alice | 30.00 |
| Bob | 80.00 |
| Carol | NULL |
Carol is included with NULL amount. Essential for finding users who have never placed an order:
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;
RIGHT JOIN: Keep All Right Rows
The opposite of LEFT JOIN — returns all rows from the right table.
SELECT u.name, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
| name | amount |
|---|---|
| Alice | 50.00 |
| Alice | 30.00 |
| Bob | 80.00 |
| NULL | 20.00 |
Order 4 is included; its user name is NULL. In practice, RIGHT JOIN is often rewritten as LEFT JOIN with tables swapped.
FULL OUTER JOIN: Union
Returns all rows from both tables. Rows without a match get NULLs on the missing side.
SELECT u.name, o.amount
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;
| name | amount |
|---|---|
| Alice | 50.00 |
| Alice | 30.00 |
| Bob | 80.00 |
| Carol | NULL |
| NULL | 20.00 |
SELF JOIN: Joining a Table to Itself
Useful for hierarchical data like employee-manager relationships.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
How ERD Relationships Map to JOINs
ERD relationship lines translate directly to JOIN ON conditions:
- 1:N relationship (users to orders): JOIN orders ON users.id = orders.user_id
- N:M relationship (students to courses via enrollments): Two JOINs through the junction table
- Nullable FK: Consider LEFT JOIN when the foreign key allows NULL
Conclusion
JOINs are the backbone of relational database querying. When you visualize table relationships in an ERD, it becomes intuitive which JOIN type to use and how to write the ON clause. Design your schema in Erdmates, then use the AI natural language query feature to generate complex JOIN queries automatically.