May 25 2010

Episode 70: Know Your Joins

Play

How well do you know your database joins? This episode we talk about the different kinds to help make you a join master.

News and Follow-ups – 00:48

  • Mark’s tinfoil hack failed

Geek Tool – 03:28

Webapps – 09:48

Topic – 18:25

  • What?
    • Combines records from two or more tables in a database
  • Why?
    • A general rule of thumb is that database joins are faster than doing separate select statements in code.
  • Types
    • Ansi Standard lists 4 types:
    • Inner
      • Join tables based on a common column Eg. PK/FK
      • Most common type of join
    • Outer
      • Combine all rows of one table with only matching rows on the other table
    • Left
      • Return all rows on the left table even if not in the right
    • Right
      • Return all rows in the right table even if not in the left
    • Cross join
      • Takes two tables and combines every row in table b with every row in table a.
      • 1000 row table would produce 1 million rows
      • Why make one then?
        • Suppose you want a grid of articles of clothing
        • select size, color from sizes cross join colors
    • MySQL University
    • A visual explanation of sql joins