CTE and Subquery, MySQL vs Postgres. Example of self-join.

mysql.sql

# MySQL
select
  E1.id,
  E1.full_name,
  E2.id as supervisor_id
  E2.full_name as supervisor_full_name
from (
  select
    id,
    first_name || ' ' || last_name as full_name,
    supervisor_id
  from people
  where person_type = 'employed'
) E1
left join (
  select
    id,
    first_name || ' ' || last_name as full_name,
    supervisor_id
  from people
  where person_type = 'employed'
) E2 ON E1.supervisor_id = E2.id

postgres.sql

# Compare the above with Postgres
with employees as (
  select
  id,
  first_name || ' ' || last_name as full_name,
  supervisor_id
  from people
  where person_type = 'employed'
)
select
  E1.id,
  E1.full_name,
  E2.id as supervisor_id
  E2.full_name as supervisor_full_name
from employees E1
left join employees E2 ON E1.supervisor_id = E2.id

MySQL: just create temporary table instead WITH and use it