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.
>Solution :
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