🧠 Advanced SQL JOINS — 6 Core Types

🔗 1. INNER JOIN
✅ Definition:
Returns only the rows where there is a match between both tables.

📌 Use Case:
When you want to fetch only the common data between both tables.

sql
CopyEdit
SELECT *
FROM product p
INNER JOIN sales s ON p.product_id = s.product_id;

🔍 Output:
Only the products that have sales.

❌ Does NOT return:

●​ Products with no sales​

●​ Sales with no matching product​

🔗 2. LEFT JOIN (LEFT OUTER JOIN)

✅ Definition:
Returns all records from the left table, and matched records from the right table. If no match,
right-side fields are NULL.

📌 Use Case:
When you want to show all products, even if not sold.
sql
CopyEdit
SELECT *
FROM product p
LEFT JOIN sales s ON p.product_id = s.product_id;

🔍 Output:
●​ All products ✅​
●​ Sales info only where available ✅​
●​ Sales columns = NULL if not matched​

🔗 3. RIGHT JOIN (RIGHT OUTER JOIN)

✅ Definition:
Returns all records from the right table, and matched records from the left table. If no match,
left-side fields are NULL.

📌 Use Case:
When you want to show all sales, even if the product name is missing or deleted.

sql
CopyEdit
SELECT *
FROM product p
RIGHT JOIN sales s ON p.product_id = s.product_id;

🔍 Output:
●​ All sales ✅​
●​ Product info only where matched ✅​
●​ Product columns = NULL if no match​
🔗 4. FULL JOIN (FULL OUTER JOIN)
✅ Definition:
Returns all records from both tables, matched and unmatched. NULLs where no match.

📌 Use Case:
For full sync or comparison of both datasets.

sql
CopyEdit
SELECT *
FROM product p
FULL OUTER JOIN sales s ON p.product_id = s.product_id;

🔍 Output:
●​ Matched rows ✅​
●​ All products without sales ✅​
●​ All sales without products ✅​

●​ NULLs in either side if unmatched​

🔗 5. CROSS JOIN
✅ Definition:
Returns the cartesian product of two tables — every row from table A joins with every row from
table B.

📌 Use Case:
To generate combinations, pairing, or testing.

sql
CopyEdit
SELECT *
 FROM product p
CROSS JOIN sales s;

🔍 Output:
⚠️
7 products × 7 sales = 49 rows​
No ON condition used here

🔗 6. SELF JOIN
✅ Definition:
A table is joined with itself — useful for comparing rows within the same table.

📌 Use Case:
To find hierarchical relationships (e.g., manager-employee, product-accessory)

sql
CopyEdit
SELECT A.product_name AS Product1, B.product_name AS Product2
FROM product A
JOIN product B ON A.product_id != B.product_id;

🔍 Output:
All product pairs (excluding same product).

🔥 Full Comparison Table (Advanced View)

Feature INNER LEFT RIGHT FULL JOIN CROSS SELF JOIN
JOIN JOIN JOIN JOIN

Matches only ✅ ❌ ❌ ❌ ❌ ❌
Unmatched ❌ ✅ ❌ ✅ ❌ ❌
Left
Unmatched ❌ ❌ ✅ ✅ ❌ ❌
Right

NULLs ❌ ✅ ✅ ✅ ❌ Depends
possible

Row ❌ ❌ ❌ ❌ ✅ ✅
explosion

Uses ON ✅ ✅ ✅ ✅ ❌ ✅
Use case Matched Show all Show all Merge Combine all Compare
data left data right data everything rows same table

📚 Real-World Use Cases

Scenario Recommended JOIN

Show sold products only INNER JOIN

List all products even if not sold LEFT JOIN

Show all transactions even if product missing RIGHT JOIN

Create full audit report of both tables FULL OUTER JOIN

Generate all combinations for testing CROSS JOIN

Compare rows in same table (e.g., price SELF JOIN

gaps)

✅ Main 6 Standard Joins (recap)

1.​ INNER JOIN​

2.​ LEFT OUTER JOIN​

3.​ RIGHT OUTER JOIN​

4.​ FULL OUTER JOIN​

 5.​ CROSS JOIN​

6.​ SELF JOIN​

🚀 Extended & Special Joins (Advanced Level)

🔗 7. NATURAL JOIN (Auto-matching columns)
✅ Definition:​
Automatically joins two tables on all columns with the same name — without specifying ON
clause.

sql
CopyEdit
SELECT * FROM A
NATURAL JOIN B;

⚠️ Be careful — it auto-matches columns with the same names which can lead to unexpected
joins.

🔗 8. ANTI JOIN (Get unmatched rows)

✅ Definition:​
Returns rows from one table that do not match with another table.

✅ SQL doesn’t have a direct ANTI JOIN, but we simulate it using LEFT JOIN +
WHERE ... IS NULL

sql
CopyEdit
SELECT p.*
FROM product p
LEFT JOIN sales s ON p.product_id = s.product_id
WHERE s.product_id IS NULL;
🎯 Use case: "Show products that were never sold."

🔗 9. SEMI JOIN (Like IN, only checks existence)

✅ Definition:​
Returns rows from table A where a match exists in B, but doesn’t return B’s columns.

sql
CopyEdit
SELECT * FROM product
WHERE product_id IN (SELECT product_id FROM sales);

🎯 Used in filtering data based on presence in another table.

🔗 10. HASH JOIN / MERGE JOIN / LOOP JOIN (Execution strategy joins)
These are internal join algorithms used by SQL engines — you don’t write them directly, but
they affect performance:

Join Type Used When

Hash Join For large, unsorted tables

Merge Join When both sides are sorted

Nested For small datasets or indexed

Loop lookups

✅ Optimizers choose this automatically in SQL Server/PostgreSQL/etc.

🔗 11. RECURSIVE JOIN / CTE JOIN

✅ Used for hierarchical/tree structures like parent → child, org charts, etc.
sql
CopyEdit
WITH RECURSIVE hierarchy AS (
SELECT id, name, manager_id
 FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
INNER JOIN hierarchy h ON e.manager_id = h.id
)
SELECT * FROM hierarchy;

🎯 Real-world: File folders, employee hierarchies, comment threads, etc.

✅ Summary: Full List of SQL Joins

Join Type Purpose

INNER JOIN Matching rows only

LEFT JOIN All from left + matches

RIGHT JOIN All from right + matches

FULL JOIN All from both sides

CROSS JOIN All combinations (Cartesian product)

SELF JOIN Table joined with itself

NATURAL JOIN Auto joins using common columns

ANTI JOIN Rows with no match

SEMI JOIN Filter: only where match exists

RECURSIVE Tree-like, parent-child data

JOIN

HASH/MERGE/NE Execution engine decisions (internal)

STED
🧠 BEYOND STANDARD SQL JOINS – The Hidden World
These are not exactly new "JOIN types," but patterns, pseudo-joins, or database-specific
join variations used in advanced querying, data warehousing, or analytics.

🔗 12. APPLY JOIN (CROSS APPLY, OUTER APPLY) – SQL Server Only
✅ Similar to joins, but lets you run a subquery per row of the left table.
sql
CopyEdit
SELECT p.product_name, s.*
FROM product p
CROSS APPLY (
SELECT TOP 1 * FROM sales s WHERE s.product_id = p.product_id
ORDER BY price DESC
) s;

●​ CROSS APPLY → like INNER JOIN (only matched)​

●​ OUTER APPLY → like LEFT JOIN (includes NULLs)​

🎯 Used for:​
Row-wise subqueries, top-N per group, dynamic filtering per row.

🔗 13. LATERAL JOIN (PostgreSQL, Oracle, MySQL 8+)

✅ Same as APPLY in T-SQL, but in ANSI SQL syntax.
sql
CopyEdit
SELECT p.product_name, s.*
FROM product p
LEFT JOIN LATERAL (
SELECT * FROM sales s WHERE s.product_id = p.product_id LIMIT 1
) s ON true;
🎯 Used when:​
Subquery needs columns from outer table.

🔗 14. PARTITIONED JOIN (BigQuery, Snowflake, Hive)

✅ Joins where the join is processed in parallel based on partitions (for big data).
sql
CopyEdit
SELECT ...
FROM tableA
JOIN tableB
ON A.id = B.id
USING HASH JOIN

🎯 Used in Big Data engines for performance tuning.

🔗 15. TEMPORAL JOIN (Time-Based / Slowly Changing Dimensions)

✅ Joins where time or version is the key, not ID.
sql
CopyEdit
SELECT e.employee_id, e.name, h.dept
FROM employee_history h
JOIN employee e
ON e.employee_id = h.employee_id
AND e.join_date BETWEEN h.start_date AND h.end_date;

🎯 Use case: “What department was this employee in at a point in time?”

🔗 16. FUZZY JOIN (AI/Data Science)

✅ Joins where matching is not exact — e.g., name spellings are slightly different.
📦 Tools: Python (fuzzywuzzy), Power BI Fuzzy Matching, T-SQL soundex/levenshtein
python
CopyEdit
# In Python pandas
fuzzy_merge(df1, df2, left_on='name', right_on='name',
method='levenshtein')

🎯 Use case: Match Jon, John, Jhon, etc. in dirty data.

🔗 17. MULTI-TABLE JOIN (CHUNKED JOIN)

✅ Joining more than 2 tables in a complex chain:
sql
CopyEdit
SELECT o.order_id, c.customer_name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

🎯 Used in normalized DB with fact and dimension tables.

🔗 18. MERGE JOIN in Spark SQL / DataFrames

✅ Used internally in Apache Spark (big data) when joining sorted tables – efficient for
large-scale joins.

python
CopyEdit
df1.join(df2, "key", "outer")

🎯 You control the join strategy (broadcast, shuffle, sort-merge) for performance.

✅ FULL JOIN FAMILY – Summary

 Type Works In Use Case

INNER JOIN All DBs Match only

LEFT / RIGHT JOIN All DBs Keep all from one side

FULL OUTER JOIN All DBs (some need UNION) All from both

CROSS JOIN All DBs Combinations

SELF JOIN All DBs Compare within same table

NATURAL JOIN Oracle, MySQL Auto join on same-named

columns

ANTI JOIN All (via LEFT JOIN) Filter unmatched rows

SEMI JOIN All (via EXISTS/IN) Existence check

APPLY JOIN SQL Server Per-row subqueries

LATERAL JOIN PostgreSQL, MySQL ANSI APPLY

PARTITIONED JOIN BigQuery, Spark Parallel joins

TEMPORAL JOIN DW/BI Systems Track history/version/time ranges

FUZZY JOIN Python, Power BI Approximate matches

✅ Final Set of All Join Variants (Master List 🧠)

# Join Name Category Special Notes

1 INNER JOIN Standard Matched rows from both sides

2 LEFT JOIN Standard All from left, matched from right

3 RIGHT JOIN Standard All from right, matched from left

4 FULL OUTER Standard All from both, matched and unmatched

JOIN

5 CROSS JOIN Standard Cartesian product (combinations)

6 SELF JOIN Standard Table joins with itself

7 NATURAL ANSI SQL Auto joins on columns with same name
JOIN

8 ANTI JOIN Logical Show unmatched from one table (via LEFT JOIN +
WHERE IS NULL)

9 SEMI JOIN Logical Only check existence, don’t pull right side columns

10 CROSS APPLY SQL Server Row-wise join with subquery

11 OUTER APPLY SQL Server Like LEFT JOIN for subqueries

12 LATERAL PostgreSQL ANSI version of APPLY JOIN

JOIN

13 RECURSIVE Hierarchical CTE-based tree traversal (e.g. parent-child)

JOIN

14 MERGE JOIN Execution Optimized for sorted inputs in big data systems
Plan

15 HASH JOIN Execution Optimized for large, unsorted tables

Plan

16 NESTED LOOP Execution Used when one side is small or indexed

JOIN Plan

17 TEMPORAL Time-Based Joins with VALID_FROM and VALID_TO (e.g. SCD in

JOIN DW)

18 PARTITIONED Big Data Joins done in partitions (Hive, BigQuery, Spark)

JOIN

19 FUZZY JOIN AI/Data Match Approximate match using string similarity

20 COMPOSITE Advanced Join using multiple columns instead of one key

JOIN

21 MULTI-TABLE Advanced Join 3+ tables in a single query

JOIN

22 CONDITIONAL Custom Logic Join where additional filters apply after matching
JOIN
23 POLYMORPHIC ORM / Join where the right table could be any type (e.g.,
JOIN Abstract inheritance tables)

24 UNION JOIN Trick/Pattern No such SQL keyword, but combines rows of similar
structure via UNION ALL

🧩 BONUS: Non-JOIN techniques that act like JOINs

Technique Acts Like Use Case Example
Join?

EXISTS ✅ Check if match exists, faster for presence filtering

IN / NOT IN ✅ List-based semi/anti join

UNION ❌ Combines rows (vertically), not joining tables

PIVOT / ❌ Table reshaping, not joining

UNPIVOT

MERGE ✅ Like JOIN + UPSERT (used for sync/update)

✅ All Types of SQL Joins — with Advanced Details

🔹 1. INNER JOIN
●​ Returns: Only matching rows from both tables.​

●​ Use Case: Most common, filters out unmatched rows.​

sql
CopyEdit
SELECT p.product_name, s.price
FROM product p
INNER JOIN sales s ON p.product_id = s.product_id;
🔹 2. LEFT JOIN (Left Outer Join)
●​ Returns: All rows from the left table, plus matching rows from the right. If no match, right
side = NULL.​

sql
CopyEdit
SELECT p.product_name, s.price
FROM product p
LEFT JOIN sales s ON p.product_id = s.product_id;

🔹 3. RIGHT JOIN (Right Outer Join)

●​ Returns: All rows from the right table, plus matching rows from the left. If no match, left
side = NULL.​

sql
CopyEdit
SELECT p.product_name, s.price
FROM product p
RIGHT JOIN sales s ON p.product_id = s.product_id;

🔹 4. FULL OUTER JOIN

●​ Returns: All rows from both tables. If no match → NULL.​

sql
CopyEdit
SELECT p.product_name, s.price
FROM product p
FULL OUTER JOIN sales s ON p.product_id = s.product_id;

🔹 5. CROSS JOIN
 ●​ Returns: All possible combinations of rows (Cartesian product).​

●​ Use Case: When you need all pairwise combinations.​

sql
CopyEdit
SELECT p.product_name, s.price
FROM product p
CROSS JOIN sales s;

🔹 6. SELF JOIN
●​ Join table with itself using aliases.​

●​ Use Case: Hierarchy, parent-child data.​

sql
CopyEdit
SELECT a.employee_name, b.manager_name
FROM employee a
JOIN employee b ON a.manager_id = b.employee_id;

🔹 7. CHAINED JOIN (Joining Multiple Tables – n tables)

●​ Real-World Use: Sales + Product + Customer + Location​

sql
CopyEdit
SELECT c.customer_name, p.product_name, s.price, l.city
FROM sales s
JOIN product p ON s.product_id = p.product_id
JOIN customer c ON s.customer_id = c.customer_id
JOIN location l ON c.location_id = l.location_id;
This is how you form a "chain link" join da!🔗 ​
Useful in dashboards, analytics, or automation pipelines.

🔹 8. NATURAL JOIN (Rarely Used)

●​ Auto-joins using columns with same names.​

sql
CopyEdit
SELECT * FROM product NATURAL JOIN sales;

⚠️ Not recommended for large DBs. Can cause confusion.

🔹 9. ANTI JOIN (Not in SQL keyword, but logic using NOT EXISTS or
LEFT JOIN WHERE NULL)

●​ Returns: Rows from one table not matching in another.​

sql
CopyEdit
SELECT * FROM product p
LEFT JOIN sales s ON p.product_id = s.product_id
WHERE s.product_id IS NULL;

🔹 10. SEMI JOIN (Using EXISTS)

●​ Returns: Rows from one table if matching exists in another.​

sql
CopyEdit
SELECT * FROM product p
WHERE EXISTS (
SELECT 1 FROM sales s WHERE s.product_id = p.product_id
 );

📊 JOIN DIFFERENCE TABLE

Join Type Matching Unmatched Unmatched Total Rows NULLs
Left Right

INNER JOIN ✅ ❌ ❌ Matching No NULLs

only

LEFT JOIN ✅ ✅ ❌ All Left Right

NULLs

RIGHT JOIN ✅ ❌ ✅ All Right Left NULLs

FULL OUTER ✅ ✅ ✅ All records Both NULLs

JOIN

CROSS JOIN ❌ ❌ ❌ A × B rows No NULLs

SELF JOIN ✅ ❌ ❌ Depends No NULLs

NATURAL JOIN ✅ ❌ ❌ Matching No NULLs

ANTI JOIN ❌ ✅ ❌ Non-matchin Right

g NULLs

SEMI JOIN ✅ ❌ ❌ Filtered Left No NULLs