CODEWITHSUNDEEP
mysql
Khaihkd
Khaihkd

Reputation: 317

Select from table A which does not exist in table B with condition in table B

I have tableA

id  | name      
--------------
1   | John
2   | Alice     
3   | Bob

and table B

id  | tableA_id | setting 
--------------------------
1   | 1         |  1
2   | 2         |  0

I want to select all unique ids in table A which do not exist in table B with a condition in table B setting = 0.

Example I would like receive

id  | name      
--------------
1   | John    
3   | Bob

I can use query: 

SELECT * FROM tableA WHERE id NOT IN (SELECT tableA_id from tableB WHERE setting = 0)

But I think it is slowly because 2 tables is large. I think this query affect to performance.

how can I do this?

Upvotes: 0

Views: 993

Answers (1)

Blank
Blank

Reputation: 12378

Try this;)

select t1.*
from tableA t1
left join tableB t2
on t1.id = t2.tableA_id
where t2.setting <> 0 or t2.id is null

DEMO HERE

Or this:

select *
from tableA
where not exists (
    select 1 from tableB where tableA.id = tableB.tableA_id and tableB.setting = 0
)

DEMO HERE

Upvotes: 1

Related Questions