Home / Questions / 555045
Accepted
Luis Jair Juarez Sanchez
Asked: 2022-09-03 08:33:52 +0800 CST 2022-09-03 08:33:52 +0800 CST

How do I find differences between records from two "same" tables in MySQL?

  • 772

Hello, I have two tables in MySQL inventario1and inventario2both tables in essence should be identical, however, I want to see if there are differences between their records, but I want to do it from MySQL with some query

I have tried this, but it is not the result I expect

SELECT inventario.consecutivo FROM inventario WHERE inventario.consecutivo NOT IN (SELECT DISTINCT inventario2.consecutivo from inventario2)

I would like you to tell me the records where there are differences

Beforehand thank you very much

mysql

2 Answers

  1. You can try the minus statement as well.

    SELECT consecutivo 
FROM inventario 
MINUS 
SELECT consecutivo 
from inventario2

    With that instruction you will obtain the consecutives that are in table 1 and are not in table 2.

    If you want to get them the other way around, use the same query, flipping the order of the tables.

    • 2
  2. Best Answer

    You can try this sentence, where we use the NOT EXISTS clause , we select the desired field to compare from the two tables, we assign an identifier a,b to identify them and with where we compare that same field, thus we bring the data of the first select that is not in the second, example:

    SELECT inventario.consecutivo FROM inventario a WHERE NOT EXISTS 
(SELECT inventario2.consecutivo FROM inventario2 b
WHERE a.consecutivo= b.consecutivo);

    There are many ways, but this is the simplest in my opinion, tell me if it worked for you.

    • 1