NOT IN ... puzzled

Transact-SQL
1

hi

i was trying to give a simple example on how to do your search for skip

My idea
Create Tally table 1... to .. 100
select From your table where NOT IN ( the numbers from Tally Table )

Turning into a nightmare ..
NOT IN is not recommended

Google Search .. gives an article which suggests 4 other methods ..
Not Exists , EXCEPT ..... blah blah
nothing worked
http://www.dbatodba.com/sql-server/how-tos/typical-solutions-to-avoid-using-not-in-on-sql-server/

drop table #bb 
create table #bb
(
id int 
)

insert into #aa select 1
insert into #aa select 2
insert into #aa select 4

drop table #abc 
;WITH TallyTable AS (
SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS [N]
  FROM dbo.syscolumns tb1,dbo.syscolumns tb2 -- or you could use a large table from your ConfigMgr db if necessary
)
SELECT * into #abc FROM TallyTable;

select * from #bb where id not in ( select N from #abc )  

SELECT * FROM #bb A WHERE NOT EXISTS 
   (SELECT * FROM #abc B WHERE B.N = A.id )

image
image503×630 11.7 KB

can any one help me understand .. what the $%^#@&^* is going on

2

The tally table has unbroken sequence, and it is #bb that has gaps. So the query should be looking for rows that exist in the tally table that do not exist in the #bb table.

SELECT * 
FROM #abc AS a
WHERE 
    NOT EXISTS
    (
        SELECT *
        FROM
            #bb AS b
        WHERE
            b.id = a.N
    );