Should I Always Fully Qualify Column Names In SQL?

问题:

Out of interest when working with SQL statements should I always use the fully qualifed column name (tablename.columnname) even if only working with one table e.g.

SELECT table.column1, table.column2 FROM table

回答1:

It's better if you do - it doesn't add any complexity, and it can prevent errors in the future.

But in a well-defined system, you shouldn't have to - it's like namespaces in programming languages. The ideal is not to have conflicts, but it can clutter the code with the superfluous use of explicit names.

-Adam

回答2:

I generally follow these rules:

When using a single table, it is not necessary to use the table name prefix:

SELECT col1, col2 FROM table1

For multiple tables, use the full table name. Aliases can be confusing, especially when doing multiple joins:

SELECT table1.col1, table2.col2 FROM table1 INNER JOIN table2 on 
            table1.id = table2.id

I see many developers using table aliases, but particularly in large projects with multiple developers, these can become cryptic. A few extra keystrokes can provide a lot more clarity in the code.

If may, however, become necessary to use a column alias when columns have the same name. In that case:

    SELECT table1.col1, table2.col1 as table2_col1 FROM table1 
            INNER JOIN table2 on 
            table1.id = table2.id

回答3:

I would put this as personal preference. It would only make a difference if you started joining tables which contain duplicate column names.

Also, rather than write out the table name in full, use an alias:

SELECT t.column1, t.column2 FROM table as t

回答4:

If you are only selecting from one table I do not see the overall usefulness. If you are selecting from multiple tables, qualifying the column names would certainly make it easier to read for any other developer who may not be familiar with your database schema.

回答5:

If you're only querying one table - I'd say no. It's more readable that way.

回答6:

Don't solve problems you don't have yet. (At least that's what my team lead is always telling me.) I'm sure the monkey who someday has to add a JOIN to your statement can figure it out.

回答7:

I think it is a good idea to always use the fully qualified column name. Use an alias if the table name is too long. It also prepares your queries for futures additions of e.g. joins.

回答8:

I would say it is nice to use qualified name, it adds readability to your code. It does not make much sense to use it for single table but for multiple tables it is must. if table names are too big then it is recommended to use alias, alias should preferably be derived from table name.

SELECT Dep.Name,Emp.Name
FROM Department DEP INNER JOIN Employee Emp
ON Dep.departmentid=Emp.DepartmentID 

回答9:

No.

You should always alias the tables, and you should always qualify your column names with the table aliases.

select
    p.FirstName,
    p.LastName,
    p.SSN
from Person p
where p.ID = 345

回答10:

I prefer to use a table alias when more than 1 table is in play.

回答11:

Even the most well defined systems are subject to change. A new field could quite easily be introduced to a table causing ambiguity in an existing query.

Fully qualifying them protects you from this scenario.