Last update: 2021-10-08

Here are some tips that I hope somebody told me earlier:

1. select from multiple tables implicitly means cross join, simple "join" also means cross join. The following two SQL queries both return cross join of Parts and Suppliers.

select * from Parts, Suppliers
select * from Parts join Suppliers

1. You can use the "with" clause to define temporary tables and use it in following SQL queries. This can simplify SQL queries and easier to understand.

with
    Cheap_Parts as (select * from Parts where Parts.cost < 10),
    Expensive_Parts as (select * from Parts where Parts.cost >=10)

select * from Cheap_Parts

1. SQL does have an evaluation order. Some people might have heard about SQL optimizers and think that the order is determined by the optimizer. But that is the execution process. SQL does have a logical evaluation order, or else how can we be sure that the result would be determined? I am not talking about non-deterministic functions in SQL here. In general, the evaluation order is listed below. You can read more about it at here, here, and here

FROM -> ON -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> TOP