Sql-server – SQL To Return True False base on condition where it either matches the condition or is completely null

sql server

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.

Best Answer

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.

Related Question