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

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

Barry


As I'm using the word here I mean the FROM table not any of the JOINed ones.

Roy Lambert

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

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

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