0

I have written an SQL query which I am thinking might not be right. I have Table A(Column A_PK, B, C, D), Table B(Column A_PK, C_PK), Table C(C_PK,..) Table B is associative entity for A and C. i,e it has two columns A_PK from Table A and C_PK from table B.

I want to select All As where it has an entry in B and matches my search param for C_PK.

  SELECT * FROM 
  A a 
  WHERE EXISTS(SELECT 1 FROM B b WHERE A_PK = a.A_PK AND b.C_PK = @mysearch) 
  OR NOT EXISTS(SELECT 1 FROM B WHERE A_PK = a.A_PK

Is this fine, OR I should use left join.?

Thanks in advance for looking at it.

1 Answer 1

1

Yes, this code will work, although I would put the NOT EXISTS check before the EXISTS check if the NOT EXISTS condition will be matched very frequently (more than 10-20% of the time, depending on actual performance). Other than that, this query should do what you need it to do.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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