How to Use an Exclusion Join to Remove Rows In the Right-side Table?-CodePudding

In MySQL, I am struggling to create a LEFT OUTER JOIN properly to include all rows from the left-side table which are NOT represented in the right-side table (i.e., if a row exists on the right-side table, then exclude it from the result set).

Following is a simple example with two tables: Food (bread, strawberries, carrots) and Allergies (strawberries)

The goal is to show all Food for which no Allergy exists (i.e., the correct result set is a single row for Strawberries)

CREATE TABLE TempFood (ProductName VARCHAR(64));
CREATE TABLE TempAllergies (ProductName VARCHAR(64));
INSERT INTO TempFood (ProductName) VALUES ('Bread'), ('Strawberries'),  ('Carrots');
INSERT INTO TempAllergies (ProductName) VALUES ('Strawberries');

SELECT * FROM TempFood 
LEFT OUTER JOIN TempAllergies ON TempFood.ProductName = TempAllergies.ProductName

I know I'm making a simple mistake and would appreciate guidance on how to fix my JOIN.

CodePudding user response：

The code for you desired output is a RIGHT OUTER JOIN.

RIGHT OUTER JOIN TempAllergies ON TempFood.ProductName = TempAllergies.ProductName

If you want a table with all foods that have no allergies, the answer is - SELECT * FROM TempFood LEFT JOIN TempAllergies ON TempFood.ProductName = TempAllergies.ProductName WHERE TempAllergies.ProductName IS Null