Get value even if it doesn't exist in table from SQL SELECT statement

Question

I have a MySQL table that looks something like this:

|---ID---|---COUNTER---|
|   1    |      2      |
|   2    |      6      |
|   3    |      1      |
|   5    |      9      |
|   6    |      10     |

I'm looking for a SELECT statement that returns ID's and their COUNTER. The table only have ID's such as: 1,2,3,5,6. Is there a statement where you say: I want ID's 1 to 10 even if they doesn't exist in the table, and if the ID doesn't exist, return the ID anyway with the COUNTER value 0. For example:

|---ID---|---COUNTER---|
|   1    |      2      |
|   2    |      6      |
|   3    |      1      |
|   4    |      0      |
|   5    |      9      |
|   6    |      10     |
|   7    |      0      |
|   8    |      0      |
|   9    |      0      |
|   10   |      0      |

Do I have to create a SELECT statement that contains NOT EXIST parameters?

Thanks in advance, Steve-O

The database can't return an ID that does not exist, because it can't know it should. Do you have another table defining what valid values are? — Jan Hudec, Feb 06 '12 at 09:59
@Jan Hudec: no, and I know it's stupid that I don't have one. But, is there some way I can accomplish my goal with LEFT JOIN? — ClydeFrog, Feb 06 '12 at 10:03
"I want ID's 1 to 10" -- where is the range 1 to 10 definied? e.g. in a table? passed in as parameters? — onedaywhen, Feb 06 '12 at 11:25
I was just putting it out there as an example. Thanks to Michal Powaga solution, the range is from the lowest ID to the highest — ClydeFrog, Feb 06 '12 at 12:26

Amar Palsapure · Answer 1 · 2012-02-06T12:08:07.657

2

You can use left join to solve your issue. Read more about left join here

edited Feb 06 '12 at 12:08

answered Feb 06 '12 at 09:50

Amar Palsapure

9,590
1
27
46

There are different ways of doing same thing in sql, I had given one of the approach. – Amar Palsapure Feb 06 '12 at 11:25
-1 : Downvoter please comment. If @onedaywhen you have done it, please let me know if is this incorrect answer. – Amar Palsapure Feb 06 '12 at 11:29
I didn't say "should", "will" is suggestion and "should" is compulsory. Anyways updated my answer. – Amar Palsapure Feb 06 '12 at 12:08

score 2 · Answer 2 · answered Feb 06 '12 at 09:53

It's not very robust, but if you created a temporary table with the ID's you wanted in it, you could then left join to your table containing ID and Counter which would include all the values:

Declare @tempidtable as table ( imaginaryid int )

insert into @tempidtable ( imaginaryid ) values ( 1 )
insert into @tempidtable ( imaginaryid ) values ( 2 )
insert into @tempidtable ( imaginaryid ) values ( 3 )

select 
@temptable.imaginaryid,
ISNULL(yourothertable.counter, 0)
from @tempidtable
left join yourothertable
on @tempidtable.imaginaryid = yourothertable.id

As Tomek says you could loop over the inserts to make it easier to maintain, or possible store the ids you want as a base in another table, using this as the basis for the join rather than a temp table.

score 2 · Answer 3 · answered Feb 06 '12 at 09:56

Create a table with all possible ID's:

create table Numbers (nr int primary key);

declare i int default 1;

while i < 100000 do
    insert into Numbers (nr) values (i);
    set i = i + 1;
end while;

Then you can use left join to return all numbers:

select  n.NR
,       c.Counter
from    Numbers n
left join
        Counters c
on      c.ID = n.NR

score 2 · Accepted Answer · answered Feb 06 '12 at 10:03

Without creating a temp table:

select t.num as id, coalesce(yt.counter, 0)
from your_table yt
right join (
    select 1 as num union select 2 union select 3 union select 4 union select 5 union
    select 6 union select 7 union select 8 union select 9 union select 10
) t on yt.id = t.num
order by t.num

and bit more general:

select t.num as id, coalesce(yt.counter, 0)
from your_table yt
right join (
    select t1.num + t2.num * 10 + t3.num * 100 as num
    from (
        select 1 as num union select 2 union select 3 union select 4 union select 5 union
        select 6 union select 7 union select 8 union select 9 union select 0
    ) t1
    cross join (
        select 1 as num union select 2 union select 3 union select 4 union select 5 union
        select 6 union select 7 union select 8 union select 9 union select 0
    ) t2
    cross join (
        select 1 as num union select 2 union select 3 union select 4 union select 5 union
        select 6 union select 7 union select 8 union select 9 union select 0
    ) t3
) t on yt.id = t.num
where t.num between (select min(id) from your_table) and (select max(id) from your_table)

You can define limit by yourself here I've used min and max of id value from your_table.

@Steve-O `yt` is an alias only so you can leave as is (or change if you don't like it). Only `your_table` change to proper table name and if columns are correct this should be fine. — Michał Powaga, Feb 06 '12 at 10:14

score 1 · Answer 5 · answered Feb 06 '12 at 09:51

1

I think you will have to create (generate in loop) temporary table with the complete sequence of numbers from 1 to N (where N is the MAX(Id) of counted table). Then do left join to that table and apply GROUP BY clause.

answered Feb 06 '12 at 09:51

Tomek

3,267
2
22
23

score 1 · Answer 6 · edited May 23 '17 at 12:06

1

You need the range of integers to do an outer join with your table based on ID. Generating a range of integers is dependent on the SQL vendor if you do not want to use a temporary table. See SQL SELECT to get the first N positive integers for hints on how to do this based on your SQL vendor.

edited May 23 '17 at 12:06

Community

1
1

answered Feb 06 '12 at 09:56

Dervall

5,736
3
25
48

Get value even if it doesn't exist in table from SQL SELECT statement

6 Answers6