I would like some expert opinion on best practices when it comes to column naming.

The background is that according to Wikipedia, the following syntax,

SELECT ... FROM Employees JOIN Timesheets USING (EmployeeID);

is more efficient than

SELECT ... FROM Employees JOIN Timesheets ON (Employees.EmployeeID = Timesheets.EmployeeID);

However, the JOIN ... USING syntax only works of all primary key columns have globally unique names. Thus I wonder if this is considered The Right Thing to do.

Personally, I always used to create tables with PK column id, and foreign key column othertable_id. But that way it's not possible to use USING or NATURAL JOIN.

Any links to design styles or best practice guides for table design would be appreciated, too!

  • 3
    Wikipedia is wrong. The first version is by no means more efficient than the second. Under the hood the database will create absolut identical queries
    – user1822
    Jul 7, 2011 at 12:05

3 Answers 3


This has been asked before on SO.

Where you have common and very ambiguous names, then prefix with table name. That is, anything you're liable to have to alias in almost every query.

So for an Employee table I'd have


And Wikipedia actually says:

The USING construct is more than mere syntactic sugar, however, since the result set differs from the result set of the version with the explicit predicate. Specifically, any columns mentioned in the USING list will appear only once, with an unqualified name, rather than once for each table in the join.

That is one less column. You'd never use SELECT * anyway so the point is moot...

  • I hadn't thought of searching SO - silly me. Do you have links to any particularly good SO questions? Anyway, thanks, I shall stick to the "unique ID" naming henceforth!
    – Kerrek SB
    Jul 7, 2011 at 9:35
  • @Kerrk SB: actually, I haven't. I tend to ignore them because each one gets answered or closed quickly :-) Sorry
    – gbn
    Jul 7, 2011 at 9:40
  • I would like to see those similar questions on SO, if anyone can dig up a link. I came here because I couldn't find a question there, and I was sure it'd have been asked already. I immediately found this question though. Jul 4, 2013 at 7:40
  • It was on programmers.SE. A nice big bun fight for you... programmers.stackexchange.com/questions/114728/…
    – gbn
    Jul 4, 2013 at 8:08

In the book SQL Antipatterns: Avoiding the Pitfalls of Database Programming, using id is not recommended, and I would agree with the author.

This is a particular problem when you are doing complex reporting and need more than one id, as you then have to alias those columns. Using <tablename>_id makes it easier to identify the correct foreign key to join to (as they have the same name) and makes errors from joining to the wrong column less likely.

That said, many databases don't support the USING syntax, which makes the problem a non-issue for those databases. Nor do many databases support using a natural join which I would also not recommend using, as the join could change if the table structures change. For example, you might a column called modified_date to both tables, you would not want to join on those columns but the natural join would attempts do so.


It is better to explicitly give table name and column name like Employees.EmployeeID with expressions where a join exists

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.