SQL SELECT with 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'm interested in the values ​​1,2 for foo and 1,2,3 for bar . Is there an SQL query that returns “missing” values ​​along with 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 not, but maybe I just don't know that?

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

Answer 1

Assuming you named your Data table, below you will get the results you published.

• The WITH statement creates a temporary memory table containing all rows from 1 to the maximum that are present in your actual table.
• CROSS APPLY returns a row for each 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 

Answer 2

create the following tables

table_foo

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

table_bar

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

After that, this request 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 ) 

Answer 3

Assuming your table is called MyTab, this sql will create a second data table (I assume 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 

Answer 4

 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