MySQL UNION QUERY

🔹 Scenario

We have two tables:

• blog_posts - Stores blog post titles and metadata.
• post_contents - Stores different content sections for each post.

📌 Sample Data

1️⃣ blog_posts

id	title	author_id	created_at
1	First Post	2	2024-03-10
2	Second Post	3	2024-03-11
3	Third Post	4	2024-03-12

2️⃣ post_contents

id	post_id	content	content_type
1	1	"Intro text..."	"intro"
2	1	"Main content..."	"body"
3	2	"Another post..."	"body"

📝 SQL Query

SELECT 
    bp.id AS post_id, 
    bp.title AS post_title, 
    pc.content, 
    pc.content_type
FROM blog_posts bp
LEFT JOIN post_contents pc ON bp.id = pc.post_id

UNION

SELECT 
    bp.id AS post_id, 
    bp.title AS post_title, 
    NULL AS content, 
    'no content' AS content_type
FROM blog_posts bp
WHERE bp.id NOT IN (SELECT post_id FROM post_contents);
                

🔹 Explanation

1️⃣ The first part of the UNION query:

• Fetches blog post titles and their related content.
• Uses a LEFT JOIN so all blog posts appear, even if they don’t have content.

2️⃣ The second part of the UNION:

• Retrieves posts that have no content (ensures all posts are included).
• Assigns NULL to content and marks content_type as 'no content'.

✅ Expected Output

post_id	post_title	content	content_type
1	First Post	"Intro text..."	"intro"
1	First Post	"Main content..."	"body"
2	Second Post	"Another post..."	"body"
3	Third Post	NULL	"no content"

Alternative: Use GROUP_CONCAT for Compact Results

If you want one row per post with all content combined, use GROUP_CONCAT:

SELECT 
    bp.id AS post_id, 
    bp.title AS post_title, 
    GROUP_CONCAT(pc.content ORDER BY pc.content_type SEPARATOR ' | ') AS content
FROM blog_posts bp
LEFT JOIN post_contents pc ON bp.id = pc.post_id
GROUP BY bp.id, bp.title;
                

✅ This will output content as a single row per post.