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)