JOIN vs Union

  • 左と右をつなげたい場合(カラム追加)は => join
  • 上と下をつなげたい場合(レコード追加)は => union
SELECT
    posts.id,
    lx.is_liked,
    lx2.likes_count
FROM
    posts
LEFT OUTER JOIN(
    SELECT
        p1.id AS note_id,
        IF(l1.user_id = 1, "YES", "NO") AS is_liked
    FROM
        posts AS p1
    LEFT OUTER JOIN(
        SELECT
            *
        FROM
            likes
        WHERE
            likes.user_id = 1
    ) AS l1
ON
    l1.note_id = p1.id
GROUP BY
    p1.id,
    l1.user_id
) AS lx
ON
    lx.note_id = posts.id
LEFT OUTER JOIN(
    SELECT posts.id AS note_id,
        COUNT(*) likes_count
    FROM
        posts
    LEFT OUTER JOIN likes ON likes.note_id = posts.id
    GROUP BY
        likes.note_id,
        posts.id
    ORDER BY
        posts.id
) AS lx2
ON
    lx2.note_id = posts.id