Some SQL tips I've learned over the last few years
…even though I spent part of my career writing SQL queries in corporations.
Temporary tables with with
with
can be used to create temporary tables that can be used in the main query.
with cities as (
select name, state, population
from city
)
select *
from cities
where population > 1000000;
filter
in aggregated columns
having
is not the only way to filter aggregated columns. filter
is much simpler and doesn’t require creating subqueries for multiple aggregated columns.
select state
,count(*) as qty_cities
,count(*) filter (where population > 1000000) as qty_big_cities
from cities