SQL - Check table for new rows?

Question

I have two tables, for example:

Table A Table B ======= ======= Name | Color Name | Color ---------------------- ---------------------- Mickey Mouse | red Mickey Mouse | red Donald Duck | green Donald Duck | blue Donald Duck | blue Minnie | red Goofy | black Minnie | red 

Table A is my source table, and B is the destination table. Now I need a query that finds all the different (optional) rows in table A, so table B can be updated with these rows. So I need a query that will find me the following rows from table A:

 Name | Color ---------------------- Donald Duck | green Goofy | black 

What is a good approach for such a request? It should be as efficient as possible (avoid too many associations). Thanks for any help!

Answer 1

I would use the NOT EXISTS structure.

 SELECT Name, Color FROM TableA WHERE NOT EXISTS ( SELECT 1 FROM TableB WHERE TableA.Name = TableB.Name AND TableA.Color = TableB.Color) 

Answer 2

 SELECT a.Name, a.Color FROM a LEFT OUTER JOIN b ON (a.Name = b.Name AND a.Color = b.Color) WHERE b.Name IS NULL AND b.Color IS NULL 

Answer 3

 Select A.Name, A.Color From A left join B on A.Name = B.Name and A.Color = B.Color Where B.Name is null 

Answer 4

In SQL Server 2008, you can use the EXCEPT statement, which is used as UNION , but returns everything from the first query, except where it is also in the second:

 SELECT * FROM TABLEA EXCEPT SELECT * FROM TABLEB 

I understand that Oracle has a MINUS statement that does the same thing.

Answer 5

You can use the EXCEPT statement, which is the opposite of UNION. In Oracle, the equivalent is MINUS.

 SELECT * FROM TABLE_A EXCEPT SELECT * FROM TABLE_B 

Answer 6

I usually add the "updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" column, and I use its value to check when a new row is inserted or an existing one is changed.

In the application that I developed, I needed to solve a problem similar to yours, so I saved somewhere MAX (updated) B, and then with the request I determined all the lines where A.updated> B.updated, and the result is all new + modified lines.

Since the default value for the field is CURRENT_TIMESTAMP, and it automatically updates "ON UPDATE", you never need to explicitly set its value.

Answer 7

A NOT EXISTS subquery must be allowed by an outer join:

 SELECT Name, Color FROM TableA WHERE NOT EXISTS ( SELECT 1 FROM TableB WHERE TableA.Color = TableB.Color AND TableA.Name = TableB.Name ) 

Or you can just use the outer join directly:

 SELECT TableA.Name, TableA.Color FROM TableA LEFT OUTER JOIN TableB ON TableA.Name = TableB.Name AND TableA.Color = TableB.Color WHERE TableB.Name IS NULL 

They should be equally effective; this is a question that you find more intuitive.

Answer 8

There are already many correct answers, but I want to touch on a philosophical question:

Is this database schema really viable in a production environment within a single schema?

Does it really make sense to have two tables containing data and then write a query to compare with each other? I think it makes sense to have only one table and maybe add a date id to find records added after a certain point.

The only situation in which I can think where you want to do this is where you have two separate databases and you want to “synchronize” them, or when you want to find the differences between them, say, backup and production.

Answer 9

In Oracle, you are likely to use:

 MERGE INTO b USING (SELECT name, color FROM a) src ON (src.name = b.name AND color = src.color) WHEN NOT MATCHED THEN INSERT (name, color) VALUES (src.name, src.color); 

If your table has a primary key (do you really have tables without one?), For example NAME, and you would like to INSERT or UPDATE depending on the presence of an entry in table B, you should use:

 MERGE INTO b USING (SELECT name, color FROM a) src ON (src.name = b.name) WHEN NOT MATCHED THEN INSERT (name, color) VALUES (src.name, src.color) WHEN MATCHED THEN UPDATE SET color = src.color; 

I believe that SQL Server also has a MERGE statement or similar.

Answer 10

SELECT TableA.Name, TableA.Color FROM TableA WHERE TableA.Name + TableA.Color NOT IN (SELECT TableB.Name + TableB.Color FROM TableB)

Answer 11

 INSERT INTO B SELECT a.Name, a.Color FROM A a LEFT JOIN B b ON a.Name = b.Name AND a.Color = b.Color WHERE b.Color IS NULL AND b.Name IS NULL