Sql statement to compare two table I have two table with the same structure, i want an sql statement to compare them and see any modified line, deleted or added

Table Product1 :

 ________________
|Product  | Price|
|Product1 | 10   |
|Product2 | 8    |
|Product3 | 9    |
|________________|


Table Product2 :

 ________________
|Product  | Price|
|Product1 | 11   |
|Product2 | 8    |
|Product3 | 9    |
|Product4 | 13   |
|________________|


Table result :

 ________________
|Product  | Price|
|Product1 | 11   |
|Product4 | 13   |
|________________|
You want something like this (see below for DDL and DML). This sort of query would be easier with the likes of EXCEPT and MINUS, but we'll have to wait for those...

-- first query gets price difference between tables
SELECT p1.* FROM product1 p1, product2 p2
WHERE p1.pname = p2.pname
AND p1.price &lt;&gt; p2.price
-- second query finds extra products in product2 table - unions both of them.
UNION
SELECT p2.* FROM product2 p2, product1 p1
WHERE p2.pname NOT IN(SELECT pname FROM product1);

+----------+-------+
| pname    | price |
+----------+-------+
| Product1 |    10 |
| Product4 |    13 |
+----------+-------+


Or - for generality, note where I've added 'Product5', 17 to the product1 table? To get the differences both ways you need to perform the UNION of 3 queries

SELECT p1.* FROM product1 p1, product2 p2
WHERE (p1.pname = p2.pname
AND p1.price &lt;&gt; p2.price)
UNION 
SELECT p1.* FROM product1 p1, product2 p2
WHERE p1.pname NOT IN (SELECT pname FROM product2)
UNION
SELECT p2.* FROM product2 p2, product1 p1
WHERE p2.pname NOT IN(SELECT pname FROM product1);


which gives

+----------+-------+
| pname    | price |
+----------+-------+
| Product1 |    10 |
| Product5 |    17 |
| Product4 |    13 |
+----------+-------+

CREATE TABLE product1
(
  pname VARCHAR(15),
  price int,
  PRIMARY KEY (pname)
);

INSERT INTO product1 VALUES('Product1', 10); 
INSERT INTO product1 VALUES('Product2', 8); 
INSERT INTO product1 VALUES('Product3', 9); 
INSERT INTO product1 VALUES('Product5', 17);    

CREATE TABLE product2

(
  pname VARCHAR(15),
  price int,
  PRIMARY KEY (pname)
);

INSERT INTO product2 VALUES('Product1', 11); 
INSERT INTO product2 VALUES('Product2', 8); 
INSERT INTO product2 VALUES('Product3', 9); 
INSERT INTO product2 VALUES('Product4', 13);