SQL tips for performance optimization

[rta_thiendao]

1. SQL query order of operations:

Source: https://www.periscopedata.com/blog/sql-query-order-of-operations

2. WHERE clause is iterated on each row
   -> subquery should not be placed in WHERE
   -> The more complicated the WHERE clause is, the lower the performance the SQL query has, especially when the row numbers are large enough.

3. FROM:
   -> minimize the data to load from FROM
   -> If JOIN with 1 subquery needs to be made, only SELECT necessary columns in the subquery
   -> Avoid shortening SQL query by creating middle query
   -> Base table should be load from FROM rather than the other query result

4. Cases of JOIN: