MYSQL: UNION results between two tables where omitting records from first table if PK found in second table

I have two tables products and product_edits which hold product information on the pricelist. My app works in a way that if user changes any product info in products table it inserts it into product_edits table…

PRODUCTS table

pk|code|name     |description|price|....
-----------------------------------
1 |QW1X|Product 1|...
2 |LW1X|Product 2|...
3 |DE1X|Product 3|...

PRODUCT_EDITS table

pk|product_id|code|name              |description|price|....
-----------------------------------
1 |         2|LW1X|Product 2 new name|...

In above case I would like an SQL that returns records from both tables, but if product is found in product_edits table it selects only from product_edits and not also from products table.

I tried using standrd union but selects all records from both tables:

select code, name, description from products
union
select code, name, description from product_edits

Answer

It’s better to use EXISTS instead of IN, in this case. You want the search to stop once you found a match, not go over all of the results from product_edits.

So do it like this:

SELECT
    code, name, description
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM product_edits e WHERE e.code = p.code)
UNION
SELECT
    code, name, description
FROM product_edits