Listing Non-Record

Question

I have two tables.

1st table:

called kimlik

  id        ad
 ---------------
 1         ahmet
 2         mehmet
 3         ali

2nd table:

called siparis

 id        kimlikid     tarih                      miktar
 ------------------------------------------------------------
 1         1              22.09.2011           10 
 1         2              22.09.2011           100

I want to list via SQL query to persons who doesn't give an order on 22.09.2011.

Result:

 ad             tarih    
 --------------------------
 ali           22.09.2011  

Answer 1

Some methods:

NOT IN

DECLARE @NoDateWanted date = '22.09.2011';

SELECT ad, @NoDateWanted as tarih
FROM
    kimlik k
WHERE
    k.ID NOT IN (SELECT kimlikid FROM siparis s WHERE s.tarih = @NoDateWanted)

NOT EXISTS

DECLARE @NoDateWanted date = '22.09.2011';

SELECT ad, @NoDateWanted as tarih
FROM
    kimlik k
WHERE
    NOT EXISTS(SELECT 'kimlik' FROM siparis s 
               WHERE k.id = s.kimlikid 
                     and s.tarih = @NoDateWanted)

LEFT JOIN

DECLARE @NoDateWanted date = '22.09.2011';

SELECT ad, @NoDateWanted as tarih
FROM
    kimlik k
    left join siparis s 
         on s.kimlikid = k.id
         and s.tarih = @NoDateWanted
WHERE s.id IS NULL

The method you choose is usually down to personal preference and performance (they can produce different query plans depending on your data volumes), ie: NOT EXISTS is frequently faster than LEFT JOIN if siparis has 1,000s of rows per kimlik id.

EDIT: Added on variable to compare and display in result set

Answer 2

The 4th way is to use EXCEPT (aka MINUS) where the RDBMS supports it.
This should give the same execution plan as NOT EXISTS

DECLARE @NoDateWanted date = '20110922';

SELECT id, @NoDateWanted AS tarih
FROM kimlik
EXCEPT
SELECT id, @NoDateWanted 
FROM siparis
WHERE tarih = @NoDateWanted;

You'll have to add the date filter in as a constant.
When you check for "no rows" there is no row to pull the data from of course.

Answer 3

SELECT k.ad, s.tarih
FROM kimlik k
JOIN siparis s on k.kimlikid = s.id
WHERE k.id NOT IN (
         SELECT si.kimlikid 
         FROM si.siparis
         WHERE si.tarih = '22.09.2011')