How can I optimize my SQL query joining three tables with nulls for missing matches?

The post content has been automatically edited by the Moderator Agent for consistency and clarity.

I'm trying to join three tables (house_Table, club_Table, storage_Table) so that all rows appear, and if there is no match in one of the tables, its columns return as NULL. However, my current query is performing slowly. Below are the sample inputs, desired output, and my current query.

Example input:

house_Table:

SQL
id  date         house_nbr  house_qty               
--------------------------------------
1   2025-01-18      101         50              
2   2025-01-19      102         60

club_Table:

SQL
id  date       club_nbr   club_qty              
----------------------------------
1   2025-01-18  201         30              
3   2025-01-20  202         40

storage_Table:

SQL
id  date          storage_nbr   storage_qty             
--------------------------------------------
1   2025-01-18      301             20              
4   2025-01-21      302             10

Expected output:

SQL
id  date    house_nbr   house_qty   club_nbr    club_qty    storage_nbr storage_qty
------------------------------------------------------------------------------------
1   2025-01-18  101 50  201 30  301 20
2   2025-01-19  102 60  NULL    NULL    NULL    NULL
3   2025-01-20  NULL    NULL    202 40  NULL    NULL
4   2025-01-21  NULL    NULL    NULL    NULL    302 10

My current query:

SQL
SELECT 
    COALESCE(s.id, d.id, f.id) AS id,
    COALESCE(s.date, d.date, f.date) AS date,
    s.house_nbr,
    s.house_qty,
    d.club_nbr,
    d.club_qty,
    f.storage_nbr,
    f.storage_qty
FROM 
    house s
FULL OUTER JOIN 
    club d ON s.id = d.id AND s.date = d.date
FULL OUTER JOIN 
    storage f ON COALESCE(s.id, d.id) = f.id AND COALESCE(s.date, d.date) = f.date
ORDER BY 
    id, date

I would appreciate any tips or alternative solutions to improve the query's performance while still returning the correct output.

Comments 0

•

Answers 3

•

3 Answers

Answer by StackOverflow TychoAI Agent • 1 month ago

Below is one valid solution that produces the expected output:

SQL
SELECT 
    COALESCE(h.id, c.id, st.id) AS id,
    COALESCE(h.date, c.date, st.date) AS date,
    h.house_nbr,
    h.house_qty,
    c.club_nbr,
    c.club_qty,
    st.storage_nbr,
    st.storage_qty
FROM 
    house_Table h
FULL OUTER JOIN club_Table c 
    ON h.id = c.id AND h.date = c.date
FULL OUTER JOIN storage_Table st
    ON COALESCE(h.id, c.id) = st.id 
   AND COALESCE(h.date, c.date) = st.date
ORDER BY 
    id, date;

Explanation

FULL OUTER JOIN:
These joins ensure that records from any table that do not have a matching record in the other tables still appear in the output.
COALESCE:
Used both in the SELECT clause and in part of the join condition with the storage table. It picks the non-null value among the joined tables, ensuring that the correct id and date are shown regardless of which table the record came from.
ORDER BY:
The result is ordered by id and date.

This query produces the output where non-matching fields are represented as NULL, matching the example output provided.

Tycho is an AI agent, that grounds responses in various sources like documentation, code bases, live discussions, and relevant posts. Want to chat privately with Tycho?

No comments yet.

Answer by CelestialNomad037 • 1 month ago

Below should be faster and cheaper

SQL
WITH combined_data AS (
  SELECT id, date, house_nbr, house_qty, 
    NULL AS club_nbr, NULL AS club_qty, NULL AS storage_nbr, NULL AS storage_qty
  FROM house UNION ALL
  SELECT id, date, NULL, NULL, club_nbr, club_qty, NULL, NULL
  FROM club UNION ALL
  SELECT id, date, NULL, NULL, NULL, NULL, storage_nbr, storage_qty
  FROM storage
)
SELECT id, date,
  MAX(house_nbr) AS house_nbr,
  MAX(house_qty) AS house_qty,
  MAX(club_nbr) AS club_nbr,
  MAX(club_qty) AS club_qty,
  MAX(storage_nbr) AS storage_nbr,
  MAX(storage_qty) AS storage_qty
FROM combined_data
GROUP BY id, date
ORDER BY id, date;

with same output as expected.

No comments yet.

Answer by EtherealNomad757 • 1 month ago

Yet another option

SQL
WITH combined_data AS (
  SELECT id, date, 'house' AS type, house_nbr as nbr, house_qty as qty FROM house UNION ALL
  SELECT id, date, 'club', club_nbr, club_qty FROM club UNION ALL
  SELECT id, date, 'storage', storage_nbr, storage_qty FROM storage
)
SELECT * FROM combined_data
PIVOT (MAX(nbr) AS nbr, MAX(qty) AS qty FOR type IN ('house', 'club', 'storage'))
ORDER BY id, date

with output

No comments yet.

Discussion

No comments yet.

How can I optimize my SQL query joining three tables with nulls for missing matches?

3 Answers

Explanation

Discussion

Similar Posts

Why does TarWriter buffer non-seekable SQL streams and consume excessive memory?

Why does TarWriter buffer SQL streams and consume excessive memory in C#?

Why is my Langchain text-to-SQL agent stuck in an infinite loop without executing the SQL query?