SQL joins summarized:
SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.id
id name id name
-- ---- -- ----
1 Aa 2 Aa
3 Bb 4 Bb
Inner join produces only the set of records that match in both T1 and T2.
FULL OUTER JOIN T2
ON T1.id = T2.id
id name id name
-- ---- -- ----
1 a 2 a
2 b null null
3 c 4 c
4 d null null
null null 1 e
null null 3 f
Full outer join produces the set of all records in T1 and T2, with matching records from both sides where available. If there is no match, the missing side will contain null.
LEFT OUTER JOIN T2
ON T1.id = T2.id
id name id name
-- ---- -- ----
1 P 2 P
2 M null null
3 N 4 N
4 S null null
Left outer join produces a complete set of records from T1, with the matching records (where available) in T2. If there is no match, the right side will contain null.
INNER JOIN
SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.id
id name id name
-- ---- -- ----
1 Aa 2 Aa
3 Bb 4 Bb
Inner join produces only the set of records that match in both T1 and T2.
FULL OUTER JOIN
SELECT * FROM T1FULL OUTER JOIN T2
ON T1.id = T2.id
id name id name
-- ---- -- ----
1 a 2 a
2 b null null
3 c 4 c
4 d null null
null null 1 e
null null 3 f
Full outer join produces the set of all records in T1 and T2, with matching records from both sides where available. If there is no match, the missing side will contain null.
LEFT OUTER JOIN
SELECT * FROM T1LEFT OUTER JOIN T2
ON T1.id = T2.id
id name id name
-- ---- -- ----
1 P 2 P
2 M null null
3 N 4 N
4 S null null
Left outer join produces a complete set of records from T1, with the matching records (where available) in T2. If there is no match, the right side will contain null.
