How to compare rows in two identical tables and return the result of a set of missing rows?

Question

I looked through all the related questions, but not one of them does exactly what I need. I have two identically structured tables (id, VoucherNbr, BalanceInit) - one from our current production system (tableA), and the other from an inherited client system (tableB). Table A has entries a, b, c, d, e (for example), and table B has a, b, c, d, e, f, g. Table B will always have all the values ​​that exist in table A, but also have additional rows. I need to return a result set containing all the rows in table B that do not exist in table A (f and g using an example.) How can I do this?

EDIT:

TABLE A

ID | VoucherNbr | BalanceInit ============================================= 1 | 1111111111111111 | 25.00 2 | 2222222222222222 | 50.00 3 | 3333333333333333 | 10.00 

TABLE B

 ID | VoucherNbr | BalanceInit ============================================= 15 | 1111111111111111 | 25.00 17 | 1212121212121212 | 15.00 22 | 2222222222222222 | 50.00 34 | 3333333333333333 | 25.00 41 | 3232323232323232 | 75.00 

I need to return - this is a result set containing only rows that do not exist in table A, where this value in the VoucherNbr field does not exist, and not the ID field.

 ID | VoucherNbr | BalanceInit ============================================= 17 | 1212121212121212 | 15.00 41 | 3232323232323232 | 75.00 

Answer 1

Some databases offer this function directly using the SUBTRACT or MINUS operation. MySQL does not seem to have such an operation.

I would probably do this:

 SELECT B.id, B.voucherNbr, B.balanceInit FROM tableB B WHERE NOT EXISTS (SELECT * FROM tableA A WHERE A.voucherNbr = B.voucherNbr AND A.balanceInit = B.balanceInit) 

However, here are a few other MySQL solutions (I searched them on Google), you can check the speed:

 SELECT id, voucherNbr, balanceInit FROM tableB WHERE (voucherNbr, balanceInit) NOT IN (SELECT voucherNbr, balanceInit FROM tableA); SELECT id, voucherNbr, balanceInit FROM tableB LEFT JOIN tableA USING (voucherNbr, balanceInit) WHERE tableA.voucherNbr IS NULL 

Honestly, I like the first googled solution better than mine if the execution time is similar or better.

Note. I modified them from my original answer to reflect the fact that you indicate in the comment to another answer that the identifiers change in two tables.

Answer 2

Note. Assuming id is PK for both tables.

 SELECT B.* FROM TableB B LEFT OUTER JOIN TableA A ON B.id = A.id WHERE A.id IS NULL 

EDIT: based on further suggestions from OP, since Id for different rows in different tables are different, considering that the matching criteria are based on comparing two column values

 SELECT B.* FROM TableB B LEFT OUTER JOIN TableA A ON B.VoucherNbr = A.VoucherNbr AND B.BalanceInit = A.BalanceInit WHERE A.VoucherNbr IS NULL 

EDIT2: if the whole voucher number is enough, then balanceInit can also be dropped

 SELECT B.* FROM TableB B LEFT OUTER JOIN TableA A ON B.VoucherNbr = A.VoucherNbr WHERE A.VoucherNbr IS NULL 

Answer 3

You can use INTERSECT stament

 SELECT * FROM tableA INTERSECT SELECT * FROM tableB 

-EXCEPT returns any single values ​​from the left query that are also not found in the correct query.

-INTERSECT returns any various values ​​returned as a query on the left and right sides of the INTERSECT operand.

http://msdn.microsoft.com/en-us/library/ms188055.aspx