Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.


 ad             tarih    
 ali           22.09.2011  
share|improve this question
up vote 5 down vote accepted

Some methods:


DECLARE @NoDateWanted date = '22.09.2011';

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


DECLARE @NoDateWanted date = '22.09.2011';

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


DECLARE @NoDateWanted date = '22.09.2011';

SELECT ad, @NoDateWanted as tarih
    kimlik k
    left join siparis s 
         on s.kimlikid =
         and s.tarih = @NoDateWanted

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

share|improve this answer
+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… – gbn Oct 4 '11 at 7:57
+1 good answer (although none of the 3 give the result in the Q) – Jack Douglas Oct 4 '11 at 8:49
thanks @JackDouglas now fixed :-) – Andrew Bickerton Oct 4 '11 at 9:21

Following on from Andrew Bickerton's answer...

The 4th way is to use EXCEPT 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
SELECT id, @NoDateWanted 
FROM siparis
WHERE tarih = @NoDateWanted;

Edit: Now with added corrections.

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

share|improve this answer
do you mean select id from kimlik except select select id from siparis where tarih = '22.09.2011'? – Jack Douglas Oct 4 '11 at 8:42
@Jack Douglas: thanks. Yes I did. However, it was wrong in other ways – gbn Oct 4 '11 at 8:43
+1 except aka minus – Jack Douglas Oct 4 '11 at 8:53
+1 I like that additional option – Andrew Bickerton Oct 4 '11 at 9:22
SELECT, s.tarih
FROM kimlik k
JOIN siparis s on k.kimlikid =
         SELECT si.kimlikid 
         FROM si.siparis
         WHERE si.tarih = '22.09.2011')
share|improve this answer
-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 posting your answer, you agree to the privacy policy and terms of service.

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