Geek Answers Handbook

Mysql retrieves all records described in state, even if it does not exist in the table

I have a table like the following:

my_table

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

And I will say that I have such a request

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

Is it possible to get the result as follows 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 did not find a solution.

Can anyone suggest a solution?

+8
sql database mysql select
source share
3 answers

Unfortunately, mysql does not have a built-in function that generates a series, as many other databases do). There are (at least) two ways to do this:

Rigidly set the required values ​​as a subquery, then left join to the 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 it is tedious and difficult to code and maintain.

Or (as I did before) create a table (once) and fill in 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)

After setting up and filling in a large number of numbers, this approach is very convenient.

You can create a similar table filled with dates, used in a similar way, which is very convenient for creating numbers for each date in a range when not all dates have data.

+8
source share

You can create a series of numbers without having to create an additional table or without recording conditions for each value that you had to search. You can use the rownum variable, initialize with a value of 0 and increase it by 1 to easily create a series using "limit". I used the INFORMATION_SCHEMA.COLUMNS table so that you can create a large series (you can use any large table in your database 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') ;

Request 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) |
+2
source share

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

I performed the test myself and it returned the name, as expected, from 1 to 4, but when I put one id that does not exist, the mysql query will not return anything for this id, because it does not exist in the 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 really want this table, I think you can try some stored procedures to format the output table. Here you can find out more about this. http://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html and here is 'IF' in 'SELECT' - select the output value based on the value column

0
source share

More articles:


All Articles