From: Daevid Vincent
Date: February 25 2005 1:14am
Subject: Is there something broken with IN()? WAS: How can I find the records that are NOT IN the list
List-Archive: http://lists.mysql.com/mysql/180537
Message-Id: <200502250118.j1P1IwIg007266@daevid.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Now I'm really confused. I decided to just brute force this while I waited
for a real answer. I threw in a couple test numbers I knew would fail
5555555 and 66666. How come none of the values aside from the two I threw in
for good measure are failing?! Is there something broken with "IN()" on
v4.0.18, for pc-linux-gnu on i686

<?php
$foo = array (5555555, 11704, 10144, 11842, 11299, 11192, 11563, 11378,
10343, 66666, 10752, 10626, 11916, 11456, 11813, 11337, 10219, 11356, 11800,
10243, 10251, 11357, 11353, 11267, 12110, 200044, 11875, 11060, 10233,
10865, 10264, 10857, 10659, 10266, 10987, 11317, 11868, 10858, 11087, 11088,
10260, 11321, 11350, 10247, 10258, 10248, 11232, 10588, 11086, 11828, 10055,
11347, 10278, 11349, 10261, 11499, 11351, 11316, 12284, 12240, 12265, 11340,
10708, 11041, 11853, 12255, 11507, 11788, 10067, 10888, 11875, 11333, 10867,
10938, 11030, 10654, 10538, 10918, 11068, 11237, 11060, 10127, 10495, 10035,
10294, 10173, 10098, 10282, 10383, 10049, 10076, 10277, 10106, 10541, 10176,
10131, 10252, 11051, 11152, 11932, 11318, 10886, 10605, 10029, 11857, 10549,
10329, 11510, 10539);
echo "foo = ".count($foo)."<BR>";

$i = 1;
foreach ($foo as $k => $v)
{
	echo $i++." ";
	$sth = SQL_QUERY('SELECT id FROM mytable WHERE id = '.$v.' LIMIT 1',
true);
	if ($sth)
	{
		if (SQL_NUM_ROWS($sth) < 1) echo "<B>CANNOT FIND:
".$v."</B><BR>";
	}
}
?>

foo = 109
1 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 5555555
LIMIT 1
CANNOT FIND VULN: 5555555
2 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11704
LIMIT 1
3 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10144
LIMIT 1
4 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11842
LIMIT 1
5 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11299
LIMIT 1
6 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11192
LIMIT 1
7 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11563
LIMIT 1
8 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11378
LIMIT 1
9 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10343
LIMIT 1
10 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 66666
LIMIT 1
CANNOT FIND VULN: 66666
11 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10752
LIMIT 1
12 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10626
LIMIT 1
13 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11916
LIMIT 1
14 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11456
LIMIT 1
15 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11813
LIMIT 1
16 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11337
LIMIT 1
17 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10219
LIMIT 1
18 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11356
LIMIT 1
19 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11800
LIMIT 1
20 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10243
LIMIT 1
21 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10251
LIMIT 1
22 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11357
LIMIT 1
23 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11353
LIMIT 1
24 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11267
LIMIT 1
25 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 12110
LIMIT 1
26 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 200044
LIMIT 1
27 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11875
LIMIT 1
28 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11060
LIMIT 1
29 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10233
LIMIT 1
30 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10865
LIMIT 1
31 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10264
LIMIT 1
32 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10857
LIMIT 1
33 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10659
LIMIT 1
34 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10266
LIMIT 1
35 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10987
LIMIT 1
36 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11317
LIMIT 1
37 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11868
LIMIT 1
38 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10858
LIMIT 1
39 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11087
LIMIT 1
40 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11088
LIMIT 1
41 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10260
LIMIT 1
42 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11321
LIMIT 1
43 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11350
LIMIT 1
44 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10247
LIMIT 1
45 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10258
LIMIT 1
46 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10248
LIMIT 1
47 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11232
LIMIT 1
48 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10588
LIMIT 1
49 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11086
LIMIT 1
50 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11828
LIMIT 1
51 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10055
LIMIT 1
52 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11347
LIMIT 1
53 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10278
LIMIT 1
54 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11349
LIMIT 1
55 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10261
LIMIT 1
56 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11499
LIMIT 1
57 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11351
LIMIT 1
58 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11316
LIMIT 1
59 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 12284
LIMIT 1
60 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 12240
LIMIT 1
61 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 12265
LIMIT 1
62 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11340
LIMIT 1
63 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10708
LIMIT 1
64 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11041
LIMIT 1
65 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11853
LIMIT 1
66 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 12255
LIMIT 1
67 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11507
LIMIT 1
68 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11788
LIMIT 1
69 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10067
LIMIT 1
70 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10888
LIMIT 1
71 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11875
LIMIT 1
72 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11333
LIMIT 1
73 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10867
LIMIT 1
74 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10938
LIMIT 1
75 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11030
LIMIT 1
76 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10654
LIMIT 1
77 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10538
LIMIT 1
78 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10918
LIMIT 1
79 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11068
LIMIT 1
80 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11237
LIMIT 1
81 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11060
LIMIT 1
82 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10127
LIMIT 1
83 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10495
LIMIT 1
84 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10035
LIMIT 1
85 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10294
LIMIT 1
86 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10173
LIMIT 1
87 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10098
LIMIT 1
88 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10282
LIMIT 1
89 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10383
LIMIT 1
90 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10049
LIMIT 1
91 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10076
LIMIT 1
92 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10277
LIMIT 1
93 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10106
LIMIT 1
94 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10541
LIMIT 1
95 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10176
LIMIT 1
96 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10131
LIMIT 1
97 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10252
LIMIT 1
98 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11051
LIMIT 1
99 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11152
LIMIT 1
100 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11932
LIMIT 1
101 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11318
LIMIT 1
102 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10886
LIMIT 1
103 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10605
LIMIT 1
104 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10029
LIMIT 1
105 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11857
LIMIT 1
106 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10549
LIMIT 1
107 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10329
LIMIT 1
108 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11510
LIMIT 1
109 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10539
LIMIT 1
total = 110


> -----Original Message-----
> From: Daevid Vincent [mailto:daevid@stripped] 
> Sent: Thursday, February 24, 2005 4:20 PM
> To: mysql@stripped
> Subject: How can I find the records that are NOT IN the list
> 
> I have an "IN()" list of 107 IDs (PK) out of about 6000 possible.
> 
> I do this query and I get 105 rows back. 
> 
> I want to know which two [107 - 105 = 2] of the IDs in the 
> "IN()" list are
> absent?
> 
> # 105 rows 
> SELECT count(*)
> FROM mytable  
> WHERE id IN (11704, 10144, 11842, 11299, 11192, 11563, 11378, 
> 10343, 10752,
> 10626, 11916, 11456, 11813, 11337, 10219, 11356, 11800, 
> 10243, 10251, 11357,
> 11353, 11267, 12110, 200044, 11875, 11060, 10233, 10865, 10264, 10857,
> 10659, 10266, 10987, 11317, 11868, 10858, 11087, 11088, 
> 10260, 11321, 11350,
> 10247, 10258, 10248, 11232, 10588, 11086, 11828, 10055, 
> 11347, 10278, 11349,
> 10261, 11499, 11351, 11316, 12284, 12240, 12265, 11340, 
> 10708, 11041, 11853,
> 12255, 11507, 11788, 10067, 10888, 11875, 11333, 10867, 
> 10938, 11030, 10654,
> 10538, 10918, 11068, 11237, 11060, 10127, 10495, 10035, 
> 10294, 10173, 10098,
> 10282, 10383, 10049, 10076, 10277, 10106, 10541, 10176, 
> 10131, 10252, 11051,
> 11152, 11932, 11318, 10886, 10605, 10029, 11857, 10549, 10329, 11510,
> 10539);
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=daevid@stripped
>