Skip to content

Instantly share code, notes, and snippets.

@nvquanghuy
Last active Nov 15, 2020
Embed
What would you like to do?
CTE and Subquery, MySQL vs Postgres. Example of self-join.
# 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
# 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
@virusandy
Copy link

virusandy commented May 12, 2020

MySQL: just create temporary table instead WITH and use it

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment