SQL SELECT with a default value

Question

Assuming I have an SQL table with this schema:

CREATE TABLE(
foo INTEGER,
bar INTEGER,
baz INTEGER DEFAULT 0
);

which contains data

foo|bar|baz
-----------
1  |1  |2
2  |3  |4

and I am interested in values 1,2 for foo and 1,2,3 for bar. Is there an SQL request which would return "missing" values along with the existing ones:

foo|bar|baz
-----------
1  |1  |2
1  |2  |0
1  |3  |0
2  |1  |0
2  |2  |0
2  |3  |4

? I suspect there isn't, but perhaps I just don't know it?

UPDATE: 1) using SQLite 3; 2) the missing values are given by the DEFAULT clause; 3) the second table shows the expected result.

Answer 1

Assuming you named your table Data, following would get you the results you've posted.

• the WITH statement creates a temporary in memory table containing all Bar's from 1 to the Maximum present in your actual table.
• the CROSS APPLY returns a row for every bar, existing in your table or not.
• the CASE statement selects an existing baz if present, 0 if not.

SQL Statement

WITH q AS (
  SELECT  [bar] = 1
          , [MaxBar] = MAX(bar)
  FROM    Data
  UNION ALL 
  SELECT  q.bar + 1
          , q.MaxBar
  FROM    q
  WHERE   q.bar + 1 <= q.MaxBar
)  
SELECT  Data.foo
        , q.bar
        , CASE WHEN q.bar = Data.bar THEN Data.baz ELSE 0 END
FROM    q
        CROSS APPLY Data
ORDER BY
        Data.foo
        , q.bar

Test script

WITH Data AS (
  SELECT [foo] = 1, [bar] = 1, [baz] = 2
  UNION ALL SELECT 2, 3, 4
), q AS (
  SELECT  [bar] = MIN(bar)
          , [MaxBar] = MAX(bar)
  FROM    Data
  UNION ALL 
  SELECT  q.bar + 1
          , q.MaxBar
  FROM    q
  WHERE   q.bar + 1 <= q.MaxBar
)  
SELECT  Data.foo
        , q.bar
        , CASE WHEN q.bar = Data.bar THEN Data.baz ELSE 0 END
FROM    q
        CROSS APPLY Data
ORDER BY
        Data.foo
        , q.bar