SQL Query Formatting

Question

SELECT `accounts`.`password` FROM accounts WHERE `accounts`.`user`='some_user'

SELECT password FROM accounts WHERE user='some_user'

I am a bit confused about the two. I know as far as the results, there is not a thing different between the two. However is there some reason to do one way as opposed to the other? I learned it the second way but have had some people tell me the first way is the 'official' way to do this.

Answer 1

You'll need to use the table.field format if you are performing a JOIN and need to use it to disambiguate two fields with identical names in different tables. For example:

   SELECT table1.name 
     FROM table1
LEFT JOIN table1 ON table1.id = table2.id
    WHERE table2.name='smith'

As far as using the grave accent (`) goes, you'll need it if your tables or fields are reserved SQL keywords such as select, where, and, etc.

Answer 2

The only thing different is that you are adding the table names to the query. I personally like to do this for later changes. If you were to join in another table later that also had a password field the second statement would have an ambiguous column name.

Answer 3

As long as you understand when you must qualify columns with table names (when more than one table is involved) and when you must use identifier quoting (when your identifier is a reserved word to the SQL engine processing your query) then I believe it's safe and easier to read to use your version than the fully-qualified and quoted version.