SQL Joins

Stardate 60101.2
Got a phone interview on Tuesday night. I really prefer face to face, and that I know ahead of time. This one also came in the middle of getting the boys ready for scouts. Needless to say, I didn’t do well. But I did get a succinct definition of the difference between an inner and outer join. An inner join is the standard

SELECT TABLE_A.COLUMN1, TABLE_A.COLUMN2, TABLE_B.COLUMN3
FROM TABLE_A, TABLE_B
WHERE TABLE_A.COLUMN1 = TABLE_B.COLUMN1

This will only return rows where COLUMN1 matches in TABLE_A and TABLE_B. If there are entries in TABLE_A that do not have a match in TABLE_B, these rows are not returned. This is where an outer join comes in.

(ORACLE Syntax)
SELECT TABLE_A.COLUMN1, TABLEA.COLUMN2, TABLEB.COLUMN3
FROM TABLE_A, TABLE_B
WHERE TABLE_A.COLUMN1 = TABLE_B.COLUMN1(+)

This will return COLUMN1 and COLUMN2 in all rows from TABLE_A, and any matches from TABLE_B COLUMN3.

So an inner join only gets rows where the tables have matches, and the outer join will return rows from TABLE_A whether a match exists with TABLE_B or not. I know it, but this is a nice, simple explanation. I gotta remember it.

End of Entry