Here is the situation, I have a DB2 table with 5 rows as shown below
I need to perform a DELETE operation in such a way that I only retain rows which contain PRDN in the column SUBS and PDB01 in column DBNM, i.e row 2 and 3. Well, it might have been a question back those days when you are new to SQL and need to break your head to figure out how to fullfil the requirement.
I don't need rows any other rows to be present in the table other than row 2 and 3, Let us first at a DELETE DML -
DELETE * FROM TABLE WHERE SUBS='TEST' AND DBNM='TDB01' ;
Result Table -
Well this DML will only DELETE 1 row ( Row number 4 ) as it is an AND operation. AND operation in a DELETE DML will perform the delete(s) on the table when BOTH the conditions in the WHERE clause are satisfied/true. Row numbers 4 and 5 were not deleted as one of the conditions failed
Let's look at the second DELETE DML -
DELETE * FROM TABLE WHERE SUBS='TEST' OR DBNM='PDB02' ;
Result Table -
Perfect DML, The OR clause on a DELETE DML will perform the delete(s) on the table on the rows which satisfy any one or both the conditions. It will delete rows where SUBS='TEST' ( row 4 and 5 ), it will also delete rows where DBNM='PDB02' ( row 1 ) . Row number 2 and 3 are retained.
This example had just 5 rows and the requirement was pretty simple, This post was only meant to make the users feel and understand the difference in the OR and AND clause. In case you have large number of records and need to filter it out, use the LIKE clause as appropriate as shown in below example -
DELETE * FROM TABLE WHERE SUBS='TEST' OR DBNM NOT LIKE 'PDB01' ;
This query will also delete row number 1, 4 and 5.
Note -
Like clause can cause performance overhead on queries and is not recommended.
