Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jun 2010
    Posts
    293
    Thanks
    63
    Thanked 8 Times in 8 Posts

    Does order matter on table joins?

    Let's say I have two tables as follows:
    Code:
    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?

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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:
    Code:
    SELECT
      lakeName
    FROM
      lakes
    INNER JOIN
      fish
    ON
      lakes.lakeID = fish.lakeID
    AND
      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.

  • Users who have thanked guelphdad for this post:

    XmisterIS (07-15-2011)

  • #3
    Regular Coder
    Join Date
    Jun 2010
    Posts
    293
    Thanks
    63
    Thanked 8 Times in 8 Posts
    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.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,869
    Thanks
    79
    Thanked 4,418 Times in 4,383 Posts
    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.

  • Users who have thanked Old Pedant for this post:

    XmisterIS (07-16-2011)

  • #5
    Regular Coder
    Join Date
    Jun 2010
    Posts
    293
    Thanks
    63
    Thanked 8 Times in 8 Posts
    Thanks, I am still learning about types of join - I can see how the knowledge will be extremely important when my datasets get large.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •