JOIN style/efficiency question

L

liunx

Guest
Is there any functional or efficiency difference between a query like this:

SELECT *
FROM table1, table2
WHERE table1.id = table2.id

and a query like this:

SELECT *
FROM table1
JOIN table2
ON table1.id = table2.id

?

or is does this just come down to matter of preference?I haven't noticed any difference in speed. just looks cleaner to me.which looks cleaner to you?oh sorry, the join looks cleanerI've asked this question to dba's and technology teachers.... and only found that it seems to be a preference of style.

I personally place the join in the where clause, but also haven't seen any perceivable difference in runtime.I thought! it was that you should generally refrain from using conditions which restrict the rows returned when using ON, opting for WHERE instead in those instances. Performance wise I should think there is little, if anything, in it.

Now, what about USING()? :)I think they mean when you do this

SELECT *
FROM table1
JOIN table2
ON table1.id = 'something'

that is restricted to the where statment.
but doing it where table1.id = table2.id is fine. this board has a bunch like that.Database efficiencies most people dont adhear to:

  1. DONT USE *'s - Many DBA's will nail people on this - it's not efficient at all
    Use joins when needed (here's the answer to original question)
    Dont reference recordset fields with a text string (recSet("Field0")) - rather, use ordinals
    Avoid columns that allow NULLs
    Always store recordset values to variables in your code before doing comparisons
    [/list=1]

    If you want to know reasoning on thses, just reply with questions and I'll try to explain em out.

    Hope it helps.In addition to the above:

    For an efficiently designed database you need to concentrate on a number of main areas (imho), some of which are decisions you take at the outset:
    ensure your database is in 3NF (third normalised format)
    set the best column type for the data it will store (e.g. char instead of varchar (where possible))
    understand fully, and implement, indexes (indices?)!!! <- often overlooked or not understood

    These three things will help you on your way to a most efficient solution.Torrent, I was considering going into that sort of depth, but didn't want to try to give too much more information than was needed.

    If you like that sort of stuff, check out these articles:
    ASPFAQ.com article on more efficient web pages (and databases) (<!-- m --><a class="postlink" href="http://www.aspfaq.com/show.asp?id=2424">http://www.aspfaq.com/show.asp?id=2424</a><!-- m -->)
    ASPFAQ.com article on why not to use SELECT * (<!-- m --><a class="postlink" href="http://www.aspfaq.com/show.asp?id=2096">http://www.aspfaq.com/show.asp?id=2096</a><!-- m -->)
    ASPFAQ.com article on why not to use NULLs (<!-- m --><a class="postlink" href="http://www.aspfaq.com/show.asp?id=2073">http://www.aspfaq.com/show.asp?id=2073</a><!-- m -->)

    Here's some of the articles I hinted toward.

    <!-- w --><a class="postlink" href="http://www.ASPFAQ.com">www.ASPFAQ.com</a><!-- w --> is a good site in general (even if you dont use ASP) for databases.Originally posted by putts
    Database efficiencies most people dont adhear to:

    1. DONT USE *'s - Many DBA's will nail people on this - it's not efficient at all

      [/list=1]

      If you want to know reasoning on thses, just reply with questions and I'll try to explain em out.

      Hope it helps.
      yes please explain that one. why?

      if I can use *'s in my code on a query the has 3 joins in it, it will save me about 10k of mor espace I will do it. adding all that referecne to table rows is not needed when you can save that muuch space.Firstly, when it creates the recordset, the database server actually has to make 2 queries. One to figure out the names and datatypes of the columns, and then a second to actually return the data.

      Secondly, through the web page, it's significantly quicker to use ordinals to retrieve data from a recordset than to use a string value representing the name of the column. However, if you use * you have no idea of which columns are returned in what order.

      This does present the problem of having to code like this:


      <%
      set recSet = db.execute("Select Name,Phone,Address,Zip from UserInfo")
      %>

      Your name is <%=recSet(0)%>
      Your phone is <%=recSet(1)%>
      You live at <%=recSet(2) & ", "& recSet(3)%>


      At first it's not hard to remember what ordinal goes with what column, but when you look back at this in a few days, you will have to keep bouncing from Select Statement to Code to figure it out.

      Hence, I use Constants representing the column ordinals:


      <%
      set recSet = db.execute("Select Name,Phone,Address,Zip from UserInfo")

      CONST USER_NAME = 0
      CONST USER_PHON = 1
      CONST USER_ADDR = 2
      CONST USER_ZIP = 3
      %>

      Your name is <%=recSet(USER_NAME)%>
      Your phone is <%=recSet(USER_PHON)%>
      You live at <%=recSet(USER_ADDR) & ", "& recSet(USER_ZIP)%>


      This way when you look at your code, it's as easy as reading this:


      Your name is <%=recSet("NAME")%>
      Your phone is <%=recSet("PHONE")%>
      You live at <%=recSet("ADDRESS") & ", "& recSet("ZIP")%>


      However, to your database server, it's much quicker.see that is different than php. sure we can define constants but we can't redefine them at any point in the code. once they are defined that is it we are stuck with them.

      set recSet = db.execute("Select Name,Phone,Address,Zip from UserInfo")

      CONST USER_NAME = 0

      so that in php would be

      $query = mysql_query("Select Name,Phone,Address,Zip from UserInfo");
      while($row = mysql_fetch_array($query))
      define(USER_NAME, $row[0]);

      but once USER_NAME has been defined it is always that, we can't change it or update it. so it is just easier to do this

      $username = $row["name"];

      but that is not the question. when I have 3 joins in one query I am going to use * so I don't have to list every table column that I want to use, especially if each table has 10+ columns. that is a lot of wasted space when I can do * to list all of them.

      it maybe more efficient in ASP, but my experience in php its just better to list everything with a *. besides that way I know everything from that table is being used. but in short, yes I do use the names if I only select 2 items from the table.On the constants, you aren't returning a value to the constant.
      The constant just represents the number 0. It just makes it easier when looking through your code to see recSet(USER_NAME) and know what it is than seeing recSet(0) and having to look at your SQL string to figure out what was returned first.

      P.S. For all you PHP fellas, when you see <%=recSet(USER_NAME)%> that's a quick way in ASP of printing stuff to HTML - it's the same as using Response.wriet(recSet(USER_NAME)). Just shorter and less parenthesis.

      On the large Join statements, Why waste your time typing out 30 fields from 5 different tables in your SQL string? Because this will fail safe you from running into ambiguous column names causing errors.
      In other words, If I'm joining 3 tables and 2 of these tables both have a field called "NAME" then I will receive errors because of the database will be trying to return two fields both having the same name (which can be avoided using an AS statement in your SELECT).

      There are other crazy situations where certain fields have to be at the end of the SELECT statement thus forcing you to not use a * so that you can insure this doesn't happen. Of course, the chances of most of us using these special fields is prolly fairly rare.yes the only time I will list all my fields isif they have a chance to become ambiguous, and that is the only time.

      so yes I agree with you there.Scoutt,

      it's okay. Go Ahead. You can say it.... Putts, you're right. Come on, you can do it.:D


      No really though, I think our slight disagreement on this is the fact that my main role as a web developer is as a web based database developer whereas you're a pretty much straight web app/site developer.

      The big difference between the two is that I'll go out of my way to take overhead off my database server even if it's at the expense of my web server.

      Of course, this is from an intranet point of view where our SQL Server is getting hammered constantly, however from your point of view, the web server takes, probably, more abuse than the db server.

      Neither view point is necessarily wrong, it just depends on the application of it.

      And the man said: "Let there be a compromise" and there was a compromise.

      :DLet's all go back to flat files YAY! :Dahhh shucks Wally, you were right @rollover

      but yes I guess it could be the app that you were working on also, good point. I wasn't disagreeing I just didn't see the point, ok, shut up scoutt... :PScoutt,

      When in doubt just follow the advice of the great philosophers of our day and:

      Do a little dance....

      Make a little love....

      Get down tonight!

      :drunk:
 
You must log in or register to reply here.
Back
Top