Sql: Two select statements in one query

sql

join

union

sql-queries

byAnton Shumikhin·Oct 21, 2024

To blend two SELECTs, UNION comes to the rescue, it concatenates and eliminates duplicates, utilising UNION ALL, keeps all instances, duplicates included. Of course, you can always bank on JOIN to mix correlated data from two tables into a single result set.

UNION usage:

SELECT name FROM customers
UNION
SELECT email FROM subscribers; -- Mixes customer names and subscriber emails like a pro DJ

JOIN example:

SELECT customers.name, orders.amount
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id; -- Couples customers with their orders like a perfect matchmaker

UNION and JOIN are your friends in SQL, but remember, like any good friendship, it needs proper care: When using UNION, the data types and columns in each SELECT statement should match. An ORDER BY clause can be applied to sort your unified data. DataTable still a mess? Use a WHERE clause to filter and find what you need.

Adding filters and ordering to your UNION

Tailored data retrieval - get it your way

Facing a database is like staring at a buffet: too much food, too little stomach. Therefore, having the ability to filter what you need becomes essential. And yes, the UNION operator allows just this:

SELECT 'Customer' AS Type, name, created_at FROM customers 
WHERE created_at > '2020-01-01'
UNION
SELECT 'Subscriber' AS Type, name, sign_up_date FROM subscribers 
WHERE sign_up_date > '2020-01-01'
ORDER BY created_at; -- Gives you the much-needed "Sort by newest"

Embracing duplicates (if needed)

While UNION eradicates duplicates, UNION ALL saves the day when the duplicates are vital to your analysis:

SELECT 'Blog' AS Source, title FROM blog_posts 
UNION ALL 
SELECT 'News' AS Source, headline FROM news_articles; -- Because sometimes, more is more

Crafting complex SQL dishes

Incrementing COUNT(*) within a collective UNION can provide ripe, juicy insights:

SELECT author, (SELECT COUNT(*) FROM books b WHERE b.author_id = a.id) AS book_count
FROM authors a
UNION
SELECT editor, (SELECT COUNT(*) FROM articles c WHERE c.editor_id = e.id) AS article_count
FROM editors e; -- Like a balanced diet of books and articles