Is there any difference between these two SELECT statements?

Question

One of my friend told me about Statement 2, but thus far I have used Statement 1.

• Is there any difference between these two SELECT statements?

• If so, what is it?

Statement 1:

 SELECT Col1 FROM Table1;

Statement 2:

SELECT Table1.Col1 FROM Table1;

Answer 1

No difference at all.

However when you have:

Select Table1.Col1 from Table1 INNER JOIN Table2 ON (Table1.id = Table2.ref_id);

The second statement is probably a better idea since it will remove any ambiguity for Col1. In that case Col1 could be a field in either Table1 or Table2.

In fact if Table2 had a field called Col1, you are required to use the second statement.

Answer 2

There is no difference when you refer to a single table. The syntax from your 2nd example becomes important when you join two or more tables which might share column names, UserId for example. In that case you have to specify table_name.column name to tell the sql processor exactly which column you want. So, in your statement 2, it's redundant to specify the table.

Answer 3

As long as you are using a single table, there is no difference at all. The field name in the result will be taken from the field name of the table, so the table name is not visible in the result.

If you use more than one table you might need to specify which field to use:

select Table1.Col1, Table2.Col1
from Table1
inner join Table2 on Table2.Col2 = Table1.Col2

You can also use aliases for the table names to make it easier to read:

select a.Col1, b.Col1
from Table1 as a
inner join Table2 as b on b.Col2 = a.Col2

Or even without the as keyword:

select a.Col1, b.Col1
from Table1 a
inner join Table2 b on b.Col2 = a.Col2

You can also use aliases to put different names on the output fields:

select Table1.Col1 as Table1Col1, Table2.Col1 as Table2Col1
from Table1
inner join Table2 on Table2.Col2 = Table1.Col2

Or:

select Table1Col1 = Table1.Col1, Table2Col1 = Table2.Col1
from Table1
inner join Table2 on Table2.Col2 = Table1.Col2