SQL query to combine existence check and condition check

I have 2 tables, call A and B. A has a fo开发者_开发问答reign key on B. call them A_ID and B_ID respectively. But the constraint not enforced in the design. I am not supposed to change the schema. I need to delete entries from table A based on 2 conditions. 1)If table B doesn't contain A_ID 2)If some condition on B is met.

I have formed a query something like this. But I dont think its optimal. Is there a better way of doing this?

delete from A where A_ID not in (select B_ID from B where status='x' ) 
                 or A_ID not in (select B_ID from B)

You could use not exists to delete rows without a matching entry in table B. This one treats status = 'x' as if no match was found, i.e. it will delete those rows:

delete  A
where   not exists
        (
        select  *
        from    B
        where   B.B_ID = A.A_ID
                and status <> 'x'
        )

JustABitOfCode and UltraCommit told about omitting one part
furthermore, if it's a foreign key, you can say to keep deleting unwanted A in definition:

CREATE TABLE A
(
  uniqeidentifire A_ID
  , FOREIGN KEY (A_ID) REFERENCES B(B_ID) ON DELETE CASCADE
);

This will Automatically delete each A that does not have a B match
and this is more efficient

As just explained from JustABitOfCode, please remove the condition:

(select B_ID from B where status='x')

because it is redundant: the result set of the previous select, is a SUBSET of the result set of the following select:

(select B_ID from B)