Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
Picking the column order |
Wed, Aug 7 2013 9:16 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Am I correct in assuming that for any clause in an sql statement that if no table qualifier is given for a column then the engine will assume initially that the column belongs to the master table and only look in any JOINed tables if there is no column of that name in the master table?
I'm asking because for my simple query generator I was intending to leave the table name out when its the master table. Roy Lambert |
Sat, Aug 10 2013 11:30 PM | Permanent Link |
Barry | Roy,
How do you know which table the optimizer will choose as the "master table"? It is not always the first table in the query. Barry |
Sun, Aug 11 2013 3:07 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Barry
As I'm using the word here I mean the FROM table not any of the JOINed ones. Roy Lambert |
Mon, Aug 12 2013 4:53 PM | Permanent Link |
Barry | Correct.
But I don't think the primary table used to retrieve the join data is always the From table. I think it is possible the optimizer may choose one of the join tables to be the primary table. It is probably better to fully qualify the column names with the table name to eliminate any confusion as to where the value comes from. I know it's a bit of a pain, but it will eliminate errors later. Barry |
Wed, Aug 14 2013 12:04 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< Am I correct in assuming that for any clause in an sql statement that if no table qualifier is given for a column then the engine will assume initially that the column belongs to the master table and only look in any JOINed tables if there is no column of that name in the master table? >> Yes, EDB will search the FROM tables in their *specified* order for column references. The optimized order of the tables due to join optimization, etc. does *not* affect this. However, having said that, it's *always* a good idea to specify which table a column is coming from with queries containing more than one table. It prevents you from breaking your own SQL if you decide to add some extra columns later. Tim Young Elevate Software www.elevatesoft.com |
Wed, Aug 14 2013 1:21 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
><< Am I correct in assuming that for any clause in an sql statement that if >no table qualifier is given for a column then the engine will assume >initially that the column belongs to the master table and only look in any >JOINed tables if there is no column of that name in the master table? >> > >Yes, EDB will search the FROM tables in their *specified* order for column >references. The optimized order of the tables due to join optimization, >etc. does *not* affect this. > >However, having said that, it's *always* a good idea to specify which table >a column is coming from with queries containing more than one table. It >prevents you from breaking your own SQL if you decide to add some extra >columns later. No problemo, its only the master table I was wanting to leave off the qualifier for eg SELECT * FROM Companies JOIN Career AS C ON C._ID = _ID style Roy Lambert |
This web page was last updated on Monday, April 29, 2024 at 05:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |