Sql Query to fetch Resultset

Question

The data contains over 360K numbers so make sure the query is optimised.

DATABASE

TABLE consist of data

+---------+
| Styles  |
+---------+
|  1 
|  2
|  3
|  4
|  7
|  8
|  9
|  10 
+---------+

Numbers i HAVE

+--------+
|Numbers |
+--------+
| 4
| 5  
| 6 
| 9 
+--------+

Numbers NOT IN Styles are suppose 5,6 how do I get them.

Thanks

I removed the incompatible database tags. Please tag only with the database you are actually using. — Gordon Linoff, Mar 21 '17 at 22:10
Try selecting distinct bus_id and see how many values you get back. There looks like some duplicates. — Chetan, Mar 21 '17 at 22:14
Possible duplicate of [SQL NOT IN not working](http://stackoverflow.com/questions/5231712/sql-not-in-not-working) — imvp, Mar 30 '17 at 04:18

score 0 · Answer 1 · answered Mar 21 '17 at 22:22

0

Use left or right join this sample is using left joint

select b.col1, 'is missing'
from    (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) as b(col1)
        left join (values(1),(2),(3),(4),(5),(6),(9),(10)) as a(col1) on b.col1 = a.col1
where   a.col1 is null

answered Mar 21 '17 at 22:22

Jules

1,423
13
22

What is your sql server name? MS SQL, Oracle, etc? – Jules Mar 21 '17 at 22:40

score 0 · Answer 2 · answered Mar 21 '17 at 23:06

Create the ten numbers you are interested in on-the-fly and remove those present in the table. E.g.:

select row_number() over (order by bus_id) from table fetch first 10 rows only
except
select bus_id from table;

The syntax depends on the DBMS of course. You may be using a DBMS where you'd work with rownum, limit, top, minus, a values clause or whatever instead.

Sql Query to fetch Resultset

2 Answers2