Recommended way to write database query

Question

This anything wrong with this database query

select 
    abstract_author.name, 
    title, 
    affiliation_number,
    af_name 
from        
    abs_affiliation_name, 
    abstract_affiliation,
    abstracts_item,
    abstract_author,
    authors_abstract 
where 
    abstracts_item._id = authors_abstract.abstractsitem_id and  
    abstract_author._id = authors_abstract.abstractauthor_id and 
    abstract_affiliation._id = abstract_author._id and  
    abs_affiliation_name._id =  abstracts_item._id

I'm getting my expected result. But, someone said It's not recommended way or a good practice. Would you please tell me what is recommended way to write my query(I mean which have joins) ?

See this interesting discussion... http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause — Verma, Aug 01 '13 at 10:21

PaReeOhNos · Answer 1 · 2013-08-01T09:40:44.687

It's not recommended to do your joins in the where clause. Instead it's better to use explicit JOIN conditions. So your query would be

SELECT
  abstract_author.name
, title
, affiliation_number
, af_name 
FROM abstracts_item
JOIN authors_abstract ON abstracts_item._id = authors_abstract.abstractsitem_id
JOIN abstract_author ON abtract_author.id = authors_abstract.abstractauthor_id
JOIN abstract_affiliation ON abstract_affiliation._id = abstract_author._id
JOIN abs_affiliation_name ON abs_affiliation_name._id = abstracts_item.id

I'd highly recommend you using aliases on your tables though as you'll avoid confusion. In this example, if you introduced a title field to one of the other tables, the query would most likely break as it would know which table to target. I'd do something like

SELECT
  au.name
, af.title
, af.affiliation_number
, af.af_name 
FROM abstracts_item ai
JOIN authors_abstract aa ON ai._id = aa.abstractsitem_id
JOIN abstract_author au ON au.id = aa.abstractauthor_id
JOIN abstract_affiliation af ON af._id = au._id
JOIN abs_affiliation_name an ON an._id = ai.id

You'll need to change the aliases in the select bit though as I've guessed which tables they're from

score 0 · Answer 2 · answered Aug 01 '13 at 09:33

0

I recommend you to use joins and aliases as below

select aath.name, /*alias*/title, /*alias*/affiliation_number,/*alias*/af_name 
from abs_affiliation_name aan
join abstracts_item ai on aan._id =  ai._id     
join abstract_affiliation aa on  aa._id = aath._id 
join authors_abstract  aAbs on ai._id = aAbs.abstractsitem_id 
join abstract_author aath on aath._id = aAbs.abstractauthor_id

answered Aug 01 '13 at 09:33

Robert

25,425
8
67
81

Shouldn't there be an `AS` between table names and their alias? – Philipp Aug 01 '13 at 09:34

score 0 · Answer 3 · edited Jul 29 '20 at 18:25

No there is nothing wrong with your query. It is personal preference, the ANSI-89 impicit joins you have used are however over 20 years out of date, they were replaced in ANSI-92 with explicit JOIN syntax.

Aaron Bertrand has written a compelling article on why in most instances it is prefereable to use the newer join syntax, and the potential pitfalls of using ANSI-89 joins. In most cases the execution plan for both methods will be exactly the same (assuming you haven't accidentally cross joined with implict joins). It is worth noting though that on occassion Oracle will produce different execution plans and the ANSI-89 join syntax can produce the more efficient of the two. (I have seen an example of this posted in response to one of my answers but I can't find it at the moment, you'll have to take my word for it for now). I would not however use this as a reason to always use ANSI-89 joins, another key reason to use the ANSI-92 join syntax is that outer joins can be achieved with ANSI syntax, whereas the outer join syntax on implicit joins varies by DBMS.

e.g. on Oracle

SELECT  *
FROM    a, b
WHERE   a.id = b.id(+)

On SQL-Server (deprecated)

SELECT *
FROM    a, b
WHERE   a.id *= b.id

However, the following works on both:

SELECT  *
FROM    a
        LEFT JOIN b
            ON a.id = b.id

If you always use explicit joins you end up with more consistent (and in my opinion more readable) queries.

Recommended way to write database query

3 Answers3