A quick breakdown of SQL Server JOIN types. The JOIN type (and the ON clause) shapes which rows come back.
JOIN types
- INNER JOIN
- OUTER JOIN
- SELF JOIN
In the examples, TR_HEADER (H) holds overall transaction info and TR_POINT (P) stores point accrual for that transaction. P is dependent on H: rows in P only exist when H exists.
1. INNER JOIN
You can omit INNER and write JOIN alone; it behaves the same. INNER JOIN returns only rows that match on both sides.
Estimated execution plans and costs match when you use JOIN alone vs INNER JOIN.
2. OUTER JOIN
OUTER JOIN includes LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN (Oracle does not have FULL OUTER JOIN in the same form).
2-1. LEFT OUTER JOIN
- Keeps the left side as the base
- Rows in H without matching P still appear
- When ON matches but P has no value, P columns are NULL
Estimated cost is higher than INNER JOIN because more rows are returned (INNER only returns rows with values on both sides).
2-2. RIGHT OUTER JOIN
Opposite of LEFT: the right side is the base. Because P only exists when H exists, RIGHT OUTER JOIN produced the same result and cost as INNER JOIN in this schema.
2-3. FULL OUTER JOIN
Accepts both LEFT and RIGHT sides. Again, because P depends on H, output and cost matched LEFT OUTER JOIN. If the tables were not parent/child, you would see NULLs on both sides for unmatched rows.
3. SELF JOIN
A SELF JOIN joins a table to itself, so H and P are the same table. The last line of the query kept only rows where point accrual is greater than 0, i.e. where TR_POINT exists.
You might expect the same result as INNER JOIN. Estimated rows were about 3400 for LEFT OUTER JOIN, about 800 for INNER JOIN, and about 1400 for SELF JOIN, with cost similar to LEFT OUTER JOIN.
- From INNER JOIN we knew about 800 transactions had points. Because H and P are the same table, each side can appear as the base once, so rows roughly double vs INNER JOIN.
- Cost was similar to LEFT OUTER JOIN, but LEFT returned ~3400 rows while SELF JOIN returned ~1400.
- This example is not ideal for teaching SELF JOIN. Think of an employee table in a department of 10 people where only one is the team lead: a SELF JOIN can find that lead simply.
Leave a Reply