Monday, April 14, 2014

ye olde joins

Just a summary of some stuff I used to know pretty well, but it has been a while: (and also a little exercise in differences in how people visualize stuff...)

Roughly, there are for kinds. Lets say you have a table A and a table B, joined via some ID.

  • INNER JOIN - gets you rows that appear in both A and B
  • LEFT JOIN - would return rows that appeared in A, whether or not they showed up in B
  • RIGHT JOIN - same as left, but swapping A + B
  • OUTER JOIN - would return all rows from A and B, whether or not they had entries in other table
To keep track of that, I found it useful to draw it out, the chart on the left. The syntax is a little wonky, of course. Scott, who was going over this stuff with me, thought Venn diagrams made more sense- his stickie is there on the right. I find it harder to wrap my brain around it, but I'm including it here as a nice display of different learning/visualization styles. 

(this reminds me of how odd it kind of is, how much of my SQL background was Oracle's flavor. Its old PL-SQL used a kind of wonky "(+)" symbol to indicate an outer (or optional, as they thought of it) join.)

No comments:

Post a Comment