Skip to content

Instantly share code, notes, and snippets.

@nvquanghuy
Last active Mar 31, 2018
Embed
What would you like to do?
FULL OUTER JOIN, MySQL vs Postgres

I know this might not be a big deal, but it is a big deal when you need it and it isn't there.

Consider table users and user_extras that contain users' metadata information.

users:
- id
- username

user_extras:
- user_id
- gender
- etc...

Somehow your data gets inconsistent, so you need to figure out what's in one table and not in the other one, or the other way round. With OUTER JOIN you can easily do this (Postgres).

# Postgres
SELECT
  CASE WHEN U.id IS NULL THEN 'null' ELSE 'not-null' END AS user_id,
  CASE WHEN UE.user_id IS NULL THEN 'null' ELSE 'not-null' END AS user_extra_id,
  COUNT(1) as counts
FROM users U
FULL OUTER JOIN user_extras UE ON U.id = UE.user_id
GROUP BY 1, 2
ORDER BY 3 DESC

With MySQL, you need to do a LEFT JOIN, and then a UNION with another RIGHT JOIN, and to wrap all that in a sub-query in order to sort by counts. See below on how it's done in MySQL.

# MySQL
SELECT * FROM (
SELECT
CASE WHEN U.id IS NULL THEN 'null' ELSE 'not-null' END AS user_id,
CASE WHEN UE.user_id IS NULL THEN 'null' ELSE 'not-null' END AS user_extra_id,
COUNT(1) as counts
FROM users U
LEFT JOIN user_extras UE ON U.id = UE.user_id
GROUP BY 1, 2
UNION ALL
SELECT
CASE WHEN U.id IS NULL THEN 'null' ELSE 'not-null' END AS user_id,
CASE WHEN UE.user_id IS NULL THEN 'null' ELSE 'not-null' END AS user_extra_id,
COUNT(1) as counts
FROM users U
RIGHT JOIN user_extras UE ON U.id = UE.user_id
GROUP BY 1, 2
) R
ORDER BY 3 DESC
# Postgres
SELECT
CASE WHEN U.id IS NULL THEN 'null' ELSE 'not-null' END AS user_id,
CASE WHEN UE.user_id IS NULL THEN 'null' ELSE 'not-null' END AS user_extra_id,
COUNT(1) as counts
FROM users U
FULL OUTER JOIN user_extras UE ON U.id = UE.user_id
GROUP BY 1, 2
ORDER BY 3 DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment