SQL: The key thing to understand

I found that the order SQL processes commands is crucial to understanding SQL, especially for beginners.

When reading a SQL script from top to bottom, it’s not easy. What’s more, there are certain rules that are not intuitively understandable. Such as why we cannot put aggregation commands in where, but can do it by having? If we understand the order that SQL process commands it’d be much easier to read scripts and to understand the rules.

The order is as follows:

from

on

join

where

group by

having

select

distinct

order

limit

You see, unlike the way we write, SQL starts from joining forms by the using on, filter by where, and then group items. By this point we have a base form.

Now if we wanna further filter with aggregation functions we use having. By this point we have a ready-for-use form.

Then we select the exact columns we need, filter out repetitive items by distinct if necessary. By this point we have an draft output.

Then we order the form the way we like, limit the output if necessary, and finally we have a completed output.

I made up those concepts and they may sound funny but I found them very helpful in understanding the grammar. And whenever I have to read or write monster queries (long & complicated queries), I follow the order to clarify my thoughts.

I hope this is helpful!