SQL SELECT with a default value

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.

---

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

---

create following tables

table_foo

foo|
-----------
1  |
2  |

table_bar

bar|
-----------
1  |
2  |
3  |

After that, this query should do the trick:

select * from  table
UNION
(
    select 
        table_foo.foo,
        table_bar.bar,
        0 
    from
        table,
        table_foo, 
        table_bar
    where 
        table.foo != table_foo.foo and
        table.bar != table_bar.bar
)

---

Assuming that your table is called MyTab, this sql will produce the second table of data (I'm assuming MSSQL):

select 
    f.foo,
    b.bar,
    baz = coalesce(m.baz,0)
from 
    (select foo = 1 union select foo = 2) f
    cross join (select bar = 1 union select bar = 2 union select bar = 3) b
    left join mytab m on f.foo = m.foo and b.bar = m.bar

---

declare @foovalues table (foo int) -- values 1,2 for foo
insert into @foovalues values (1) 
insert into @foovalues values (2)

declare @barvalues table (bar int) -- values 1,2,3 for bar
insert into @barvalues values (1) 
insert into @barvalues values (2)
insert into @barvalues values (3)

declare @table table (foo int, bar int, baz int) -- your data
insert into @table values (1,1,2)
insert into @table values (2,3,4)

select x.foo, x.bar, isnull(baz, 0) as baz
from (select f.foo, b.bar from @foovalues f, @barvalues b) as x
left outer join @table t on t.foo = x.foo and t.bar = x.bar
order by x.foo, x.bar