I’ve seem lots of SQL coding styles over my time. Here are three most common formatting options.
ANSI 89 Equals Syntax
I hate seeing this written. Firstly you bulk up your “Where” clause area, so it’s hard to see what is filtering the data. The more tables that get added to the query the harder it is to see. It’s also very hard to convert this sort of join to a Left, Right or Full join because it’s all hidden in there where clause. I would always advise people at the very minimum to convert it to the following.
ANSI 92 Join Syntax
This query is significantly better. You can easily see that the where clause has one filter, and thats to get only paid invoices. What the authoer has also done is aligned the joins along with each other. It makes it nice to scan down the line and see the join criteria. I however still think this doesn’t show the nesting of the joins clearly. Can you swap the join for invoice_item and invoice around? The formatting hints that you can, but it would create an error. Here’s my preferred solution…
ANSI 92 Nesting Syntax
You can see with this join that there is a clear structure to the query, and by moving the joins around it would break the flow of the query. When you put more joins in a query like this you find it very easy to see where things are nested and it often leads to spotting performance optimizations.