SQl to list rows if not in another table

I hav开发者_StackOverflow中文版e the following query which have 1000 rows

select 
staffdiscountstartdate,datediff(day,groupstartdate,staffdiscountstartdate),
EmployeeID 
from tblEmployees 
where GroupStartDate < '20100301' and StaffDiscountStartDate > '20100301' 
and datediff(day,groupstartdate,staffdiscountstartdate)>1 
order by staffdiscountstartdate desc

i have the following query which have 400 rows: ie the employees in tblemployees and in tblcards

select a.employeeid,b.employeeid 
from tblEmployees a,tblCards b 
where GroupStartDate < '20100301' 
and StaffDiscountStartDate > '20100301' 
and datediff(day,groupstartdate,staffdiscountstartdate)>1 
and a.employeeid=b.employeeid 

How to list the employees which is there in tblemployees and not in tblcards?

ie is 1000-400 = 600 rows ???

select 
    a.employeeid,
    b.employeeid 
from 
    tblEmployees a
        left join
    tblCards b 
        on
            a.employeeid=b.employeeid 
where 
    GroupStartDate < '20100301' 
and 
    StaffDiscountStartDate > '20100301' 
and 
    datediff(day,groupstartdate,staffdiscountstartdate)>1 
and
    b.employeeid is null

Use a left join to join the tables and then filter where table tblCards is null.

select  
    a.employeeid 
from  
    tblEmployees a
left outer join
    tblCards b
on
    a.employeeid=b.employeeid
where  
    GroupStartDate < '20100301'  
and  
    StaffDiscountStartDate > '20100301'  
and  
    datediff(day,groupstartdate,staffdiscountstartdate)>1  
and  
    b.employeeid IS NULL

How to list the employees which is there in tblemployees and not in tblcards?

select employeeid from tblEmployees 
   where employeeid not in 
   (select employeeid from tblCards)

 SELECT emp.EmployeeID
 FROM   tblEmployees AS emp
      LEFT JOIN tblCards AS crd ON (emp.EmployeeID = crd.EmployeeID)
 WHERE  (crd.EmployeeID IS NULL)