SQL Join Question

I will be joining multiple tables all with the same columns, is there anyway so setup the query so the specific table prefix is not needed?

Or am I trapped into using 开发者_JAVA技巧select a._id,b._id....z._id ?

No. If the columns have the same name, then the database software needs to be able to distinquish between them somehow.

Certainly for SQL Server, you have to specify it so that the query is not ambiguous - trapped im afraid.

In some RDBMSs (eg Oracle) you can use JOIN ... USING ... form. For example:

select id, col1, col2 from table1 a join table2 b using (id)

is almost equivalent of:

select a.id, col1, col2 from table1 a join table2 b on a.id = b.id

But:

• JOIN ... USING behaves differently than JOIN ... ON .... Most importantly you cannot use explicit prefix with column inside using().

  In above example you cannot use a.id or b.id. So it can get more complicated if you have third table with column id which is not related to table1 and table2 id.

• Many systems don't have it (eg SQL Server) so your code will be less portable.

• Many people (including me) prefer to use explicit table aliases with columns for clarity and to avoid mistakes when editing queries later. I just use short aliases for tables and live with it.

This looks to me like a candidate for Natural Join e.g:

SELECT *
FROM   employee NATURAL JOIN department

For more details see this article "Basic query operation: the join"

Quote:

The NATURAL JOIN keyword specifies that the attributes whose values will be matched between the two tables are those with matching names; with very rare exceptions, these will be the pk/fk attributes, and they have to have matching data types (and domains) as well.

Warning: Depending on your RDBMS, the Natural Join syntax may or may not be supported. Here are some that do support it:

• MySQL: NATURAL JOIN in MySQL
• Oracle: Equijoin with the NATURAL JOIN Keywords
• PostgreSQL Natural Join

In standard SQL, you will have to prefix your columns.

Other SQL dialects might provide this functionality, though. For example, in Postgresql:

create table t1(id integer not null, t1 text not null);
create table t2(id integer not null, t2 text not null);

select id, t1, t2 from t1 join t2 using (id);

Here, id does not have to be prefixed, because it is used in the join.

In general, yes, you're stuck qualifying each duplicated column name. I assume that you don't want to do this to avoid extra typing; one way of accomplishing that is by using table aliases, ie SELECT a.id, b.id FROM mylongtablename a, myotherlongtablename b ...

If it's really that big of a problem qualifying the column names, you could always get around this by defining a view around one or more of the tables and use aliases for table names. However, this would just make the logic more difficult to see in the query, so I certainly wouldn't recommend it.

Long story short: deal with the extra typing and prefix your column names; it'll make your query easier to read and maintain in the future.

No, I dont think there is any way by which you can stop using the column names or their prefix as you mentioned, one thing is you can alias the column names, but again that is more work for you. Check the best way out.

Importance of table alias(a glimpse from the whole)

1) A shorthand notation that is handy to use if you have to reference the table(or view) again and again(table alias).It's much easier than typing the full table name each time.

e.g.

select tableA.col1 , tableA.Col2,tableB.Col1,tableC.Col2 
from  tableA
join tableB 
on tableA.id = tableB.id
join tableC
on tableC.id = tableB.id
and tableC.id = tableA.id

Can be rephrased as

select a.col1 , a.Col2,b.Col1,c.Col2 
from  tableA a
join tableB  b
on a.id = b.id
join tableC c
on c.id = b.id
and c.id = a.id

2)Removes the ambiguity of the column names

3)Improves code redeability

4)Suppose you have a derived table like

SELECT col1,col2
FROM (select *
      from mytable
      where col3 = 'somevalue') x

in which case alias is a must

Note:-

Column alias gives a meaning full name to the columns

e.g. select avg(col1) from mytable

which gives the output as

[No Column Name]
-----------------
30

can be beautify by

select avg(col1) "Average" from mytable

or

select avg(col1) AS "Average" from mytable  

Output:

Avegare
-------------
30

However, the AS keyword is optional.

Hope this gives you some insight as to why we should go with table alias

You can save a bit of typing on the joins by using ON instead of writing the column name.

SELECT a._id, b._id FROM table1 a JOIN table2 b
ON _id