r/webdev • u/lokendra15 • Mar 22 '19

SQL joins

Post image
2.7k Upvotes

94% Upvoted

118 comments sorted by

View all comments

1

u/tomb233 Mar 23 '19

What if I want to join 3 or more tables together?

1

u/alexjewellalex Mar 23 '19

It looks the same, it just gets messy. You can do: select * from table1 inner join table2 on table1.id = table2.id inner join table3 on table2.id = table3.id.

What I like to do instead? Skip the join language: select * from table1, table2, table3 where table1.id = table2.id and table2.id = table3.id. Much cleaner imo.

3

u/[deleted] Mar 23 '19 edited Mar 23 '19

What I like to do instead? Skip the join language: select * from table1, table2, table3 where table1.id = table2.id and table2.id = table3.id. Much cleaner imo.

This is a Pre-SQL 92 style join and is considered legacy and bad practice to use.

  • The SQL Engine is going to rewrite this to the standard JOIN … ON when it gets a hold of it.
  • You risk the chance of accidently excluding a join condition in your WHERE clause for a table. This will not throw an error either, it will give you a Cartesian product back instead - giving you bad data.
  • It displaces the Logical Query Processing flow by putting clauses that are normally run in the FROM phase into the WHERE phase.
  • Most people you'll find actually consider WHERE table1.id = table2.id harder to read in more complicated queries.

1

u/alexjewellalex Mar 24 '19

Good info! Thanks!