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


ANSWERS:


its 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


select code, name, description from products
where code not in(select code from product_edits)
union
select code, name, description from product_edits

You may use IFNULL function

If your tables are related, you can try something like this:

SELECT 
    p.code, IFNULL(pe.name, p.name), IFNULL(pe.description, p.description) 
from 
    products p 
    left join product_edit pe on (p.id = pe.product_id)
UNION
SELECT 
    pe2.code, pe2.name, pe2.description
from 
    product_edits pe2

The first part will give you the products that are only in products table and the products that are in both tables, but with product_edits.description.

The second part will give you the products that are only in products_edits table, because union will remove repeated records



 MORE:


 ? How can I leverage an ORM for a database whose schema is unknown until runtime?
 ? How can I leverage an ORM for a database whose schema is unknown until runtime?
 ? How can I leverage an ORM for a database whose schema is unknown until runtime?
 ? Which ORM can preserve the correspondence between db schema and domain model?
 ? how to minimize application downtime when updating database and application ORM
 ? Can we use MongoDB with ORMs we used to use with relational databases, such as linq2sql, entity framework, subsonic,...?
 ? How to use multilanguage database schema with ORM?
 ? Please suggest one .Net ORM, which supports runtime schema evolution
 ? Can the code-first features of EF 4.1 and greater be used when the DB schema is unknown until runtime?
 ? FuelPHP ORM database schema for i18n, opinions/suggestions