How to DELETE with JOIN in Oracle

Let suppose you have these Food and Groups tables and your target is to DELETE some rows based of the first one using the values of the second.

CREATE TABLE Food(
"Code" varchar(50) NOT NULL,
"Group" varchar(100) NULL,
"Quantity" int NOT NULL,
"Color" varchar(50) NULL
);

INSERT ALL
   INTO Food ("Code","Group","Quantity","Color") VALUES ('Apple', 'Fruit', 44, 'red')
   INTO Food ("Code","Group","Quantity","Color") VALUES ('Apple', 'Fruit', 1, 'yellow')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Pineapple', 'Fruit', 14, 'brown')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Apple', 'Fruit', 12, 'red')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Banana', 'Fruit', 1, 'yellow')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Tomatoes', 'Vegetables', 8, 'red')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Mortadella', 'Beef', 1, 'pink')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Cucumbers', 'Vegetables', 3, 'green')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Cigars vanille', 'Other', 1, 'black')
   INTO Food ("Code","Group","Quantity","Color") VALUES  ('Cigars', 'Other', 2, 'brown')

SELECT 1 FROM DUAL;

CREATE TABLE Groups(
	"ID" varchar(50) NOT NULL,
	"Desc" varchar(100) NOT NULL
);

INSERT ALL
   INTO Groups ("ID","Desc") VALUES ('A001', 'Fruit')
   INTO Groups ("ID","Desc") VALUES ('A002', 'Vegetables')
SELECT 1 FROM DUAL;

Example 1

You can use the following query to DELETE the values of Food using the values of Groups, without any filter conditions.

DELETE Food
WHERE EXISTS (
   SELECT *
   FROM Groups
   WHERE Groups."Desc"=Food."Group"
);

Example 2

You can use the following query to DELETE the values of Food using the values of Groups, applying a WHERE clause to the left table (which is Food in our example).

DELETE Food
WHERE EXISTS (
   SELECT *
   FROM Groups
   WHERE Groups."Desc"=Food."Group"
   AND Food2."Quantity" > 1
);

You can find an interactive version of these examples following this link .