Replace NOT IN with LEFT JOIN to obtain the same result-CodePudding

I've a doubt about the second query in PL/SQL. I've this query:

DELETE FROM tableA
WHERE id NOT IN (SELECT c_id FROM tableB WHERE tableB.c_id = tableA.id)

Is it possible or correct change for something like this?:

DELETE FROM tableA 
LEFT JOIN tableB ON tableA.id = tableB.c_id
WHERE id IS NULL

But this show an error: syntax error near at or near "LEFT"

Can anyone help me?

CodePudding user response：

Is it possible or correct change for something like this?:
DELETE FROM tableA 
LEFT JOIN tableB ON tableA.id = tableB.c_id
WHERE id IS NULL

No, it is not possible nor correct as Oracle does not support the non-standard DELETE FROM ... JOIN syntax.

You can use:

DELETE FROM tableA a
WHERE NOT EXISTS (SELECT 1 FROM tableB b WHERE b.c_id = a.id);

or:

DELETE FROM tableA
WHERE id NOT IN (SELECT c_id FROM tableB);

CodePudding user response：

MT0's answer is probably what you want. I only want to add that there is a third method that could be useful in more advanced situations:

MERGE INTO tableA
USING (SELECT tableA.rowid AS row_id
         FROM tableA
              LEFT JOIN tableB ON tableA.id = tableB.c_id
        WHERE tableB.c_id IS NULL) src
ON (src.row_id = tableA.rowid)
WHEN MATCHED THEN UPDATE SET tableA.othercol=null -- meaningless change
                  DELETE WHERE 1=1