Top 10 SQL Joins Explained with Simple Diagrams
SQL Joins are crucial for merging data from several tables in relational databases in order to provide useful outcomes. Whether you’re new to SQL or would like a better grasp of the various join types, this blog simplifies the process for you with simple illustrations and practical logic.
Now let’s explore the top ten SQL joins that all developers need to be aware of!
1. INNER JOIN
Returns records that have matching values in both tables.
📌 Syntax:
SELECT * FROM A
INNER JOIN B ON A.id = B.id;
🖼 Diagram:
[A] ●────────────● [B]
Only the overlapping part
🧠 Use Case:
Get customers who have placed orders.
🔹 2. LEFT JOIN (or LEFT OUTER JOIN)
Returns all records from the left table, and matched records from the right.
📌 Syntax:
SELECT * FROM A
LEFT JOIN B ON A.id = B.id;
🖼 Diagram:
[A] ●─────────────● [B]
Left side + Overlap
🧠 Use Case:
List all products, even those that haven’t been sold.
🔹 3. RIGHT JOIN (or RIGHT OUTER JOIN)
Returns all records from the right table, and matched ones from the left.
📌 Syntax:
SELECT * FROM A
RIGHT JOIN B ON A.id = B.id;
🖼 Diagram:
[A] ●─────────────● [B]
Overlap + Right side
🧠 Use Case:
Show all orders, even those not linked to customers.
🔹 4. FULL JOIN (or FULL OUTER JOIN)
Returns all records when there’s a match in either table.
📌 Syntax:
SELECT * FROM A
FULL JOIN B ON A.id = B.id;
🖼 Diagram:
[A] ●─────────────● [B]
All from both + Overlap
🧠 Use Case:
Combine user data from two different systems with some common users.
🔹 5. CROSS JOIN
Returns the Cartesian product (all combinations) of the two tables.
📌 Syntax:
SELECT * FROM A
CROSS JOIN B;
🖼 Diagram:
Every row in A matched with every row in B
🧠 Use Case:
Generate combinations for a quiz (e.g., all questions with all students).
🔹 6. SELF JOIN
Joins a table to itself using aliases.
📌 Syntax:
SELECT A.name, B.name
FROM employees A, employees B
WHERE A.manager_id = B.id;
🖼 Diagram:
Same table treated as two
🧠 Use Case:
Get manager and employee name pairs.
🔹 7. NATURAL JOIN
Automatically joins tables based on columns with the same name.
📌 Syntax:
SELECT * FROM A
NATURAL JOIN B;
🖼 Diagram:
Only works if matching column names exist
🧠 Use Case:
When column names are already consistent and match logically.
🔹 8. ANTI JOIN (Simulated via LEFT JOIN + WHERE NULL)
Returns rows in left table with no match in right table.
📌 Syntax:
SELECT * FROM A
LEFT JOIN B ON A.id = B.id
WHERE B.id IS NULL;
🖼 Diagram:
Left side only – excluding overlap
🧠 Use Case:
Find products with no sales or users who never logged in.
🔹 9. SEMI JOIN (Simulated via EXISTS)
Returns rows from one table where a match exists in another.
📌 Syntax:
SELECT * FROM A
WHERE EXISTS (SELECT 1 FROM B WHERE A.id = B.id);
🖼 Diagram:
Left side only – include rows if match exists
🧠 Use Case:
List customers who have placed at least one order.
🔹 10. EQUI JOIN (Type of INNER JOIN using =)
Uses the equality operator to match columns.
📌 Syntax:
SELECT * FROM A, B
WHERE A.id = B.id;
🖼 Diagram:
Same result as INNER JOIN but using WHERE clause
🧠 Use Case:
Basic relational match between two tables.
Final Thoughts
Any database administrator, backend developer, or data analyst should become proficient with SQL joins. Building effective searches and utilizing relational databases to their full potential requires knowing when and how to use these joins.
Before moving on to more complex patterns like SELF and CROSS joins, start with INNER and LEFT joins. You’ll become an expert queryer very quickly with consistent practice.
You may be interested in this:
How Backend Development Powers Modern Web Applications
Leave a Reply