Not exist in anyone

Question

I want to make a query where I select all the IDs of table A, which ids will connect to existing values of end_date in table B. I need to get the IDs of table A which will connect only to finished IDs(i.e. with existing end_date) on B table. Relation of table a and b is one to many . A can correlate to many Bs . B will always correlate to one A table.

I have made something like this:

select id 
from A 
where not exists 
(select 1 
from B 
where end_date is null 
and A.id=B.id)

Is this correct? Or is there a faster query for the same thing?

EDIT: end_date is in table B

example : In the data set:

A.id=1
B.id=1
B.bid=333 
B.end_date=null 

A.id=1
B.id=1
B.bid=334 
B.end_date=05/05/2014 

A.id=2
B.id=2
B.bid=335 
B.end_date=null 

A.id=2
B.id=2
B.bid=336 
B.end_date=null 


A.id=3
B.id=3
B.bid=337
B.end_date=04/04/2014 

A.id=3
B.id=3
B.bid=338 
B.end_date=04/04/2014`

My query should result only id=3.

what you have is correct and imho expresses your intent most clearly, for more info about the differences between `not exists`, `not in` and `left join` see http://stackoverflow.com/q/2246772/3574819 — FuzzyTree, Jul 11 '14 at 00:47

score 0 · Answer 1 · answered Jul 10 '14 at 21:36

0

Assuming your table structure is

A(id)

B(id, end_date)

Then to select all A.id where there is no b.end_date (or it is null) you can use this query

Select id
From   A
Where  id Not In (Select id From B Where end_date is Not Null)

answered Jul 10 '14 at 21:36

Cheruvian

5,628
1
24
34

Rahul · Answer 2 · 2014-07-10T21:58:50.900

0

You can probably use a LEFT JOIN like

select A.id 
from A a
left join B b
on a.id = b.id
and b.end_date is not null 
where b.id is null

edited Jul 10 '14 at 21:58

answered Jul 10 '14 at 21:37

Rahul

76,197
13
71
125

score 0 · Answer 3 · answered Jul 10 '14 at 21:39

You don't specify your DBMS, but in later versions of SQL Server, this might be faster. You will have to test based on your data:

SELECT DISTINCT A.ID
FROM A
    INNER JOIN B ON A.ID = b.ID
WHERE b.End_date IS NOT NULL
EXCEPT
SELECT B.ID
WHERE b.End_date IS NULL

EXCEPT is a set operator that returns all entries in the first set that don't exist in the second set. Doing the query this way gives you two SARGable WHERE clauses rather than one nonSARGable subquery, so it could end up faster depending on your data topography and your physical indexes.

Not exist in anyone

3 Answers3