View Full Version : Does order matter on table joins?

07-15-2011, 03:13 PM
Let's say I have two tables as follows:

create table lakes (
lakeId int primary key,
lakeName text

create table fish (
lakeId int, /* Index into the lakes table */
fishName text
Let's say I want all lakes which contain a certain type of fish (koi, for example).

So I could make the query two ways:

1) select lakeName from lakes, fish where fish.fishName = "koi" and fish.lakeId = lake.lakeId;

2) select lakeName from lakes, fish where fish.fishName = "koi" and lake.lakeId = fish.lakeId;

They are syntactically different queries but give the same results.

Is one faster than the other? Or not?

07-15-2011, 03:24 PM
both will give the same results.

As a suggestion though, switch to explicit INNER JOIN syntax. Comma join syntax can trip you up when your queries get more complicated. Easy to leave off the join condition and easy to get a syntax order when three or more tables are involved.

Switch to this instead:

lakes.lakeID = fish.lakeID
fish.fishName = "koi"

note the INNER JOIN and ON clauses are what i've changed.

Two other items are for style only. One I always use UPPER CASE for sql commands so those stand out. Secondly indenting and one item per line allows for easier reading. yours didn't require left to right scrolling but often people get in the habit of writing all code on a single line and it is harder to read the larger the query gets.

07-15-2011, 03:35 PM
Many thanks! I didn't know about the INNER JOIN, it will definitely make my queries (which are beginning to get complicated) easier to read.

Old Pedant
07-15-2011, 11:36 PM
Order *DOES* matter for joins other than INNER JOIN.

For LEFT JOIN, the first table is the independent one and the second the dependent one.

For RIGHT JOIN, it's the other way around.

07-16-2011, 08:59 AM
Thanks, I am still learning about types of join - I can see how the knowledge will be extremely important when my datasets get large.