Nested Loop Join (NLJ)

• How it works:
  
  • For each row in the outer (driving) table, the database looks up matching rows in the inner (driven) table.
  • Think of it as a double for-loop.
• When it’s good:
  
  • Outer table is small.
  • Inner table has a good index on the join column (so lookup is fast).
  • Works well with highly selective filters.
• When it’s bad:
  
  • Outer table is large, and inner lookup is not indexed → can cause millions of lookups.
• Example:

SELECT e.emp_id, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

• If employees is small and departments.dept_id is indexed, the optimizer may use NLJ.

Hash Join

• How it works:
  
  • Database builds a hash table in memory for the smaller table (build input).
  • Then it scans the larger table (probe input) and probes into the hash table for matches.
• When it’s good:
  
  • Both tables are large.
  • No useful indexes exist.
  • Join condition is equality (=).
• When it’s bad:
  
  • If available memory is small, the hash table spills to disk (slower).
  • Not good for inequality joins (<, >).
• Merge Join (Sort-Merge Join)
• How it works:
  
  • Both tables are sorted on the join key.
  • Then the database “merges” them in sorted order like merging two sorted arrays.
• When it’s good:
  
  • Both tables are already sorted (via index or ORDER BY).
  • Large datasets with equality or range conditions.
• When it’s bad:
  
  • If sorting is required (extra cost).
  • Tables are small → NLJ is usually cheaper.
• Nested Join

 (sometimes confused)

👉 Often people mean Nested Loop Join when they say “Nested Join.”

• In Oracle and other DBs, “Nested Join” ≈ “Nested Loop Join”.
• So there’s no extra type here; it’s just a naming thing.

⚖️ Which One Is Better?

Join Type	Best Case Scenario
Nested Loop	Small outer table + index on inner
Hash Join	Large tables + no indexes + equality condition
Merge Join	Both tables sorted or large range queries

How to Switch Between Join Methods

Databases usually pick the best join automatically, but you can influence:

1. Hints (Oracle / SQL Server / MySQL)

• Oracle:

SELECT /*+ USE_NL(t2) */ ...
SELECT /*+ USE_HASH(t2) */ ...
SELECT /*+ USE_MERGE(t2) */ ...

SQL Server

SELECT ...
FROM t1 INNER HASH JOIN t2 ON ...
FROM t1 INNER LOOP JOIN t2 ON ...
FROM t1 INNER MERGE JOIN t2 ON ...

• MySQL (no direct hint, but you can influence with STRAIGHT_JOIN, indexes, and optimizer hints).

Indexing Strategy

• Adding an index on join column → encourages Nested Loop.
• Removing/ignoring indexes → may force Hash Join or Merge Join.

Query Rewrite

• Changing join order or applying filters earlier can switch join method.

✅ Rule of thumb:

• If tables are small + indexed → use Nested Loop.
• If tables are huge + no index → use Hash Join.
• If tables are sorted (or need range joins) → use Merge Join.