Join two SELECT statement results

sql

join

sql-queries

database-operations

byNikita Barsukov·Sep 6, 2024

Use a Common Table Expression (CTE) JOIN:

WITH FirstCTE AS (
    SELECT id, data FROM Table1
), SecondCTE AS (
    SELECT id, info FROM Table2
)
SELECT FirstCTE.id, FirstCTE.data, SecondCTE.info
FROM FirstCTE
JOIN SecondCTE ON FirstCTE.id = SecondCTE.id;

This code utilizes CTEs cradled within a WITH clause, and then performs a JOIN operation on the common id field between them.

Advanced joining methods

With a bit more craft, you can turn the JOIN operation into a power tool:

Implementing left join

WITH PersonCTE AS (
    SELECT person_id, task
    FROM Persons
), TasksCTE AS (
    SELECT person_id, COUNT(*) AS late_tasks
    FROM Tasks
    WHERE due_date < DATE() -- Oops! Someone's procrastinated!
    GROUP BY person_id
)
SELECT PersonCTE.person_id, PersonCTE.task, COALESCE(TasksCTE.late_tasks, 0) AS late_tasks_count
FROM PersonCTE
LEFT JOIN TasksCTE ON PersonCTE.person_id = TasksCTE.person_id;

In this example, LEFT JOIN includes even those persons who have been model citizens with no late tasks. COALESCE conveniently deals with any NULL values resulting from the LEFT JOIN.

Aggregate functions and conditional statements

...
SELECT person_id, COUNT(*) AS total_tasks, SUM(CASE WHEN due_date < NOW() THEN 1 ELSE 0 END) AS late_tasks
...

This code aggregates total and late tasks per person. COUNT(*) is the SQL version of a crowd control officer, tallying up all tasks for each person. SUM(CASE WHEN...) is the strict parent, only counting those tasks submitted after due_date.

Tackling complex join scenarios

When the going gets tough, use UNION ALL and nifty SQL conditionals:

UNION ALL for inclusive results

When the shared key between your selects is playing hide and seek, use UNION ALL:

SELECT id, data FROM Table1
UNION ALL
SELECT id, info FROM Table2;

This gives you everything on the menu, duplicates included. Remember to keep your column numbers and order consistent, and use aliases to achieve unified column naming.

Conditional logic within JOIN

...
LEFT JOIN CTE2 ON CTE1.id = CTE2.id AND CTE1.condition = CTE2.condition;

Including conditional statements directly in the ON clause can work magic to shape the JOIN's outcome.

Real-world scenarios

In daily SQL operations, keep these pointers in your programming backpack:

Need for RIGHT or FULL OUTER JOIN

You may need a RIGHT JOIN if you must include all records from the RIGHT table, or a FULL OUTER JOIN for a full, unabridged version of your data:

-- RIGHT JOIN, because the right guy always comes first!
RIGHT JOIN CTE2 ON CTE1.id = CTE2.id;

-- FULL OUTER JOIN, as we leave no table unturned
FULL OUTER JOIN CTE2 ON CTE1.id = CTE2.id;

Handling rogue duplicates and elusive order

Use SELECT DISTINCT to strike off duplicates, or ORDER BY to usher your results into a pleasing arrangement:

-- DISTINCT, for when "same-same but different" doesn't cut it
SELECT DISTINCT CTE1.id, ... 

-- ORDER BY, because SQL results enjoy walking single file too
... ORDER BY CTE1.id, CTE2.info;

Merging data from disparate data sources

Have data from different table that need to play nicely together? No problem:

-- Sales meets ProductInfo, a data-driven love story!
WITH SalesData AS (
    SELECT transaction_id, amount FROM Sales
), ProductInfo AS (
    SELECT product_id, product_name FROM Products
)
SELECT SD.transaction_id, SD.amount, PI.product_name
FROM SalesData SD
JOIN ProductInfo PI ON SD.product_id = PI.product_id;