CODEWITHSUNDEEP
sqloracle-database
sbrbot
sbrbot

Reputation: 6469

LEFT JOIN - with kept empty main record

I have two related tables Cars and AddItems and the relation 1 to 0 or many between them, so there are Cars with or without additional items.

I need the query which will always result with Cars as single records and additional records for additional car items like:

CarName | ItemName
========+=========
Car1    |          <- car without additional items
Car2    |          <- car with additional items but this solo record needed as well
Car2    | Item1
Car2    | Item2
Car3    |
Car3    | Item3

If I use simple LEFT OUTER JOIN I cannot get this solo record of car when car has joined additional items.

I could get something like:

SELECT carname,NULL AS itemname FROM cars

UNION

SELECT c.carname,ai.itemname
FROM cars c
LEFT JOIN additems ai ON ai.id_car=c.id

Is there any simpler/better (joinning) way without UNION?

Upvotes: 0

Views: 78

Answers (2)

wildplasser
wildplasser

Reputation: 44250

UNION ALL doesn't do deduplication (which is needed because the cars query and the LEFT JOIN both yield the bare cars)

UNION is only costly because it removes the duplicates, often causing a sort. Not creating duplicates in the first place is cheaper.

   -- all the casrs
SELECT carname,NULL AS itemname
FROM cars

UNION ALL

   -- additional detail records, if any
SELECT c.carname,ai.itemname
FROM cars c
JOIN additems ai ON ai.id_car=c.id
ORDER BY 1,2 NULLS FIRST
        ;

Upvotes: 1

Serg
Serg

Reputation: 22811

Your version is OK. Alternatively you can use LATERAL

SELECT t.*
FROM cars c
CROSS JOIN  LATERAL (
      SELECT c.carname, null FROM DUAL
      UNION ALL
      SELECT c.carname, i.itemname 
      FROM additems i 
      WHERE i.carname = c.carname
    ) t
ORDER BY 1, 2 NULLS FIRST;

Upvotes: 1

Related Questions