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

Andrew Bickerton · Accepted Answer · 2011-10-04T09:20:58.740

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

+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 http://dba.stackexchange.com/questions/4009/the-use-of-not-logic-in-relation-to-indexes/4010#4010 — gbn, Oct 04 '11 at 07:57
+1 good answer (although none of the 3 give the result in the Q) — Jack Douglas, Oct 04 '11 at 08:49

score 5 · Answer 2 · edited Sep 06 '18 at 11:37

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.

score -3 · Answer 3 · answered Oct 04 '11 at 07:54

-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')

answered Oct 04 '11 at 07:54

DrColossos

7,187
2
32
30

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 04 '11 at 08:52

Listing Non-Record

3 Answers3

NOT IN

NOT EXISTS

LEFT JOIN