Is there any better way to write this query

Question

I designed below query for my delete operation. I am new to SQL and just wanted to check with experienced people here if it is fine or any better way to do this. I am using DB2 database

DELETE FROM TableD
    WHERE B_id IN 
     ( 
        SELECT B.B_id
          FROM TableB tB 
            INNER JOIN TableA tA
              ON tB.A_id = tA.A_id
          WHERE A_id = 123
      ) AND 
  C_id IN (1,2,3)

This has two IN clause which I am little worried and not sure if I could use EXISTS clause anywhere.

Database Structure as below:

• Table A has ONE TO MANY relation with Table B
• Table B has ONE TO MANY relation with Table C
• Table B has ONE TO MANY relation with Table D
• Table D has composite primary key ( B_id, C_id )

Table D data somewhat similar to below

   B_id|C_id
  ----------
   1   |  1
   1   |  2
   1   |  3
   2   |  4
   2   |  5
   3   |  5

Here I have to delete rows which have C_id in array of values. But since the index is a composite of B_id and D_id, I am retrieving related B_id to the particular entity of Table A by equality operator A_id=123

Answer 1

There isn't necessarily anything wrong with your method. However, a useful alternative technique to know is merge:

merge into TableD
using ( 
    select distinct 
        B.B_id
      from TableB tB 
        inner join TableA tA on
           tB.A_id = tA.A_id and
           A_id = 123
) AB
on 
    TableD.B_id = AB.B_id and
    C_id in (1,2,3)
when matched then delete;

Note that I had to use distinct on the inner query to prevent duplicate matches.

Answer 2

DELETE FROM TableD tD WHERE EXISTS ( SELECT tB.B_id FROM TableB tB WHERE A_id = 123 AND tB.B_id = tD.B_id ) AND C_id IN (1, 2, 3)

Answer 3

You can use merge like this too :

   merge into TableD
   using TableB tB
   on B.B_id = TableD.B_id
   and tB.A_id in (select A_id from TableA tA where A_id = 123)
   and C_id in (1,2,3)
   when matched then delete;