Should I Always Fully Qualify Column Names In SQL?

Question

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

Answer 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.

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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

Answer 6

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.

Answer 7

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

Answer 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

Answer 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

Answer 10

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.