FULL OUTER JOIN, MySQL vs Postgres

_outer_join.md

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.sql

# 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.sql

# 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