Mysql get all records described in in condition even if it does not exists in table

Question

Consider I have a table like the following:

my_table

+---------------+
| id  |  name   |
+---------------+
| 1   |  ABC    |
+---------------+
| 2   |  XYZ    |
+---------------+
| 3   |  PQR    |
+---------------+
| 4   |  LMN    |
+---------------+

And say I have a query like this

select * from my_table where id in (1,2,3,4,5)

Is it possible to get output like the following,by changing the query.

+---------------+
| id  |  name   |
+---------------+
| 1   |  ABC    |
+---------------+
| 2   |  XYZ    |
+---------------+
| 3   |  PQR    |
+---------------+
| 4   |  LMN    |
+---------------+
| 5   |  NULL   |
+---------------+

I tried using self JOIN and other conditions and also google'd a lot,but didn't find a solution.

Can anyone suggest a solution?

In general you cannot select from a table data which is not present in that table, and nor would you want to — Strawberry
– Strawberry, Commented Dec 20, 2014 at 10:10
@Juergen's answer is correct. He should not have deleted it. — Gordon Linoff
– Gordon Linoff, Commented Dec 20, 2014 at 11:42
But i have asked him what if i had 100's or 1000's of record.Do i need to write each of them in the query..? @GordonLinoff Linoff — 웃웃웃웃웃
– 웃웃웃웃웃, Commented Dec 20, 2014 at 11:52
@웃웃웃웃웃 . . . You need to get them into the query somehow. Usually when you have a large number like that, they are already in a table, so you can just read them using a table or query. — Gordon Linoff
– Gordon Linoff, Commented Dec 20, 2014 at 12:06

Bohemian · Accepted Answer · 2014-12-23 05:45:28Z

Unfortunately, mysql doesn't have a built in function that generates a series as many other databases do). There are (at least) two ways of doing it:

Hard code the desired values as a subquery, then left join to your table:

select x.id, t.name
from (select 1 id
  union select 2
  union select 3
  union select 4
  union select 5) x
left join my_table t on t.id = x.id

But this is tedious and hard to code and maintain.

Or (as I have done before) create a table (once) and populate with natural numbers (once) to use as a proxy series generator:

create table numbers (num int);
insert into numbers values (1), (2), (3), ... etc

then:

select n.num id, t.name
from numbers n
left join my_table t on t.id = n.num
where n.num in (1,2,3,4,5)

Once set up and populated with lots of numbers, this approach is very handy.

You can create a similar table populated with dates, used in a similar way, which is very handy for producing figures for every date in a range when not all dates have data.

Fabien TheSolution · Accepted Answer · 2014-12-23 06:23:34Z

You can create a number serie without having to create an extra table or without writing conditions for each values that needed to be searched. You can use a variable rownum, initialize with value 0 & increase it by 1 to easily create a serie by using ‘limit’. I used the INFORMATION_SCHEMA.COLUMNStable so you can create a big serie (you can use any bigger table that you have in your DB or any table large enough for your needs).

SQL Fiddle

MySQL 5.6.6 m9 Schema Setup:

CREATE TABLE my_table
    (`id` int, `name` varchar(3))
;

INSERT INTO my_table
    (`id`, `name`)
VALUES
    (1, 'ABC'),
    (2, 'XYZ'),
    (3, 'PQR'),
    (4, 'LMN')
;

Query 1:

select rownum id, name 
from (
select @rownum:=@rownum+1 as rownum
from INFORMATION_SCHEMA.COLUMNS,(SELECT @rownum:=0) r limit 5) as num
left outer join my_table on id = rownum

Results:

| ID |   NAME |
|----|--------|
|  1 |    ABC |
|  2 |    XYZ |
|  3 |    PQR |
|  4 |    LMN |
|  5 | (null) |

Community · Accepted Answer · 2017-05-23 12:23:48Z

0

you can use clause IN, as you did and as you can see here http://www.tutorialspoint.com/mysql/mysql-in-clause.htm

I have performed a test by myself and it returned the name as expected from 1 to 4, But when I put the one id that not exist mysql query will return nothing for this id, because it not exist in database.

SELECT * FROM employee_tbl WHERE id IN ( 1,2,3,4,5,6,7,8);

1   John    250
2   Ram     220
3   Jack    170
4   Jack    100
5   Jill    220
6   Zara    300
7   Zara    360

If you realy want this table, I think you could try some stored procedure to format the output table. Here you can learn more about it. http://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html and here 'IF' in 'SELECT' statement - choose output value based on column values

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Dec 23, 2014 at 6:01

IgorAlves

5,58017 gold badges63 silver badges98 bronze badges

Collectives™ on Stack Overflow

Mysql get all records described in in condition even if it does not exists in table

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related