Keep all records in "WHERE IN()" clause, even if they are not found

Question

I have the following mysql query:

SELECT id, sum(views) as total_views
FROM table
WHERE id IN (1,2,3)
GROUP BY id
ORDER BY total_views ASC

If only id 1,3 are found in the database, i still want id 2 to appear, with total_views being set to 0.

Is there any way to do that? This cannot use any other table.

From where are you getting the data to put in the "IN" clause? Like Is it that 1,2,3 is fixed or dynamically you are getting it — Sashi Kant, Nov 27 '12 at 13:40
Michaels answer is probably the best solution to this issue unless you can use PHP or something. If you can the solution become alot easier. — Shawn, Nov 27 '12 at 23:08
I just noticed the "cannot use any other table" remark. Dare I ask why? And would this restriction apply to temporary tables? (My assumption is simply that some supervisor has forbidden you to create a permanent table for this purpose.) — svidgen, Nov 28 '12 at 15:16

score 7 · Answer 1 · edited May 23 '17 at 10:26

7

This query hard-codes the list of possible IDs using a sub-query consisting of unions... it then left joins this set of ids to the table containing the information to be counted.

This will preserve an ID in your results even if there are no occurrences:

SELECT ids.id, sum(views) as total_views
FROM (
    SELECT 1 AS ID 
    UNION ALL SELECT 2 AS ID 
    UNION ALL SELECT 3 AS ID 
) ids 
LEFT JOIN table
    ON table.ID = ids.ID
GROUP BY ids.id
ORDER BY total_views ASC

Alternately, if you had a numbers table, you could do the following query:

SELECT numbers.number, sum(views) as total_views
FROM
    numbers
    LEFT JOIN table
        ON table.ID = ids.ID
WHERE numbers.number IN (1, 2, 3)
GROUP BY numbers.number
ORDER BY total_views ASC

edited May 23 '17 at 10:26

Community

1
1

answered Nov 20 '12 at 22:43

Michael Fredrickson

36,839
5
92
109

The IN() clause can have thousands of ids in it. How would I do this like that? Any way to use if/then? – Nov 20 '12 at 22:49
There is a numbers table, but its in a different database on a different server, otherwise I could just do a simple left join. – Nov 20 '12 at 22:50
1

@k1lljoy I'm assuming that you're not allowed to create a new numbers table on this server... but do you have the rights to create a temporary table? If so, you could populate a temporary table with the thousands of IDs that are being passed in, perform a `LEFT JOIN` from this table to your views table, and then do away with the big `IN` statement altogether. – Michael Fredrickson Nov 28 '12 at 14:54
Due to vast number of servers that would be running these query, each one with a its own portion of data that they will look at, creating temporary tables is also out of the question. Secondly, I would have to query the master DB for this data each time, which sort of defeats the purpose a sharded database. – Dec 03 '12 at 02:05
1

@k1lljoy, the set of ID's will reside in memory on the server either way -- whether in an IN clause, implicit UNION table, or explicit temporary table. It's only a question of in what "format" the data lives and how the query operates on it (probably most efficiently as a temporary table). – svidgen Dec 03 '12 at 03:20
You can shorten it using the binary number technique described in this answer http://stackoverflow.com/a/8349837/68105 – MK. Dec 03 '12 at 22:31

svidgen · Accepted Answer · 2012-11-28T15:14:59.183

3

Here's an alternative to Micheal's solution (not a bad solution, mind you -- even with "a lot" of ID's), so long as you're not querying against a cluster.

create temporary table __ids (
  id int unsigned primary key
) engine=MEMORY;

insert into __ids (id) values
  (1),
  (2),
  (3)
;

SELECT table.id, sum(views) as total_views
FROM __ids left join table using (id)
GROUP BY table.id
ORDER BY total_views ASC

And if your query becomes complex, I could even conceive of it running more efficiently this way. But, if I were you, I'd benchmark this option with Michael's ad-hoc UNION'ed table option using real data.

edited Nov 28 '12 at 15:14

answered Nov 27 '12 at 23:21

svidgen

13,744
4
33
58

This would be what I would choose to do. However not sure the WHERE clause is required in the SELECT (the selected IDs are already taken care of by the JOIN). – Kickstart Nov 28 '12 at 09:52
1

Ah, touche. I'll edit that out. I'll also add the temporary keyword, which I seem to have neglected. – svidgen Nov 28 '12 at 15:14
+1... This is the implementation of the temporary table that I was too lazy to write. – Michael Fredrickson Nov 29 '12 at 22:21
This would require populating this temporary table with a relatively large amount of data which will have to be transferred from the master DB, which defeats the whole purpose. – Dec 03 '12 at 02:07
Going out on a limb here, I'd say you're wrong! You have to "transfer" the list of ID's through the connection either way. It's just a question of which of three basic "formats" you choose to do that in: an explicit temporary table, an implicit temporary table (the UNION solution), or as an IN clause, leaving you to solve the problem application-side. – svidgen Dec 03 '12 at 03:16
@svidgen Can you please help me to solve this [question](https://stackoverflow.com/questions/44926109/need-where-in-which-work-as-and-for-each-value-with-join-query-in-codeigniter) – always-a-learner Jul 06 '17 at 04:32

score 1 · Answer 3 · answered Nov 20 '12 at 22:51

1

in @Michael's answer, if you do have a table with the ids you care about, you can use it as "ids" in place of Michael's in-line data.

answered Nov 20 '12 at 22:51

Rob Starling

3,868
3
23
40

score 0 · Answer 4 · answered Nov 20 '12 at 23:05

0

Check this fiddle... http://www.sqlfiddle.com/#!2/a9392/3

Select B.ID, sum(A.views) sum from tableB B
 left outer join tableA A
 on B.ID = A.ID
group by A.ID

also check http://www.sqlfiddle.com/#!2/a1bb7/1

answered Nov 20 '12 at 23:05

Nilam Doctor

491
7
18

score 0 · Answer 5 · answered Dec 03 '12 at 11:13

try this

    SELECT id 
                (CASE 1
                    IF EXISTS THEN  views = mytable.views END
                    IF NOT EXIST THEN views = 0 END
                CASE 2
                    IF EXISTS THEN  views = mytable.views END
                    IF NOT EXIST THEN views = 0 END
                CASE 3
                    IF EXISTS THEN  views = mytable.views END
                    IF NOT EXIST THEN views = 0 END), sum(views) as total_views
    FROM mytable
    WHERE id IN (1,2,3)
    GROUP BY id
    ORDER BY total_views ASC

score 0 · Answer 6 · answered Dec 03 '12 at 22:18

0

Does it have to be rows or could you pivot the data to give you one row and a column for every id?

SELECT
  SUM(IF (id=1, views, 0)) views_1,
  SUM(IF (id=2, views, 0)) views_2,
  SUM(IF (id=3, views, 0)) views_3
FROM table

answered Dec 03 '12 at 22:18

batwad

3,588
1
24
38

Keep all records in "WHERE IN()" clause, even if they are not found

6 Answers6

Linked