3

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  
5

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

  • 2
    +1 FYI NOT EXISTS is safest because of NULLs and NOT IN. And quicker because it is a semi-join rather then join with filter. See dba.stackexchange.com/questions/4009/… – gbn Oct 4 '11 at 7:57
  • 1
    +1 good answer (although none of the 3 give the result in the Q) – Jack Douglas Oct 4 '11 at 8:49
5

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.

-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')
  • 1
    -1 in general this would return too many rows and will give the 'wrong' tarih - it should always be '22.09.2011' according to the sample result – Jack Douglas Oct 4 '11 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.