What? A blog?‎ > ‎

Common SQL JOIN Patterns

posted Oct 5, 2012, 12:48 PM by Daniel Gomes   [ updated Oct 5, 2012, 12:55 PM ]

This chart is something I really need from time to time so I'll just leave it here for further reference. :)

Lots of people go crazy on SQL statements sometimes just to reproduce one of these patterns. They even dare to write fancy PL/SQL scripts because they don't fully understand how to efficiently fetch the information by applying one of these patterns.

Oracle spent zillions of dollars to optimize the engine so you don't have to manually write procedures or complex queries for these cases! Use the respective join clause! Sometimes you even code worst logic with higher O(n) than if you let Oracle (or your preferred engine) to optimize it.

For example, consider you want all records from table A that has no related record in table B (the second case, top to bottom at the left side). You can do it both ways efficiently:

SELECT * from tran a LEFT JOIN tran_acty b ON a.id = b.id WHERE b.id is NULL;

SELECT * FROM tran a WHERE NOT EXISTS (SELECT 0 FROM tran_acty b WHERE a.id = b.id);

Well, as you can see, using the LEFT JOIN is more elegant since it has no need for a sub select and code is cleaner.

Regarding speed and optimization, both cases have the same execution plan so efficiency wise it doesn't matter.

But I've seen people writing Perl scripts just to do the same... well, see my point? :)