sql query to get new data across tables

Question

I want to query two tables as below to get the rows in the new table that does not already have a name or code entry in the current table.

table current

name           code
japan          233
india          65
england        44


table new

name           code
japan          233
india          65
england-main   44
japan          2331
india          652
usa            1

In this exampe it should return

name         code
usa          1

what sql query will archive thisenter code here

Answer 1

SELECT name, code 
FROM new 
WHERE name NOT IN (SELECT DISTINCT name FROM current)
AND code NOT IN (SELECT DISTINCT code FROM current);

No idea what flavour of SQL you're using though, so this query might differ or not work at all.

Post-edit: this query will check if the name and code exist in the current table. However, it does not check if they are in the same record.

Answer 2

select * from
(select 
    n.*,
    c.code as old_code
from 
    new as n left join 
    current as c on n.code = c.code) as T
where T.old_code is null

OR simply

select * from new as n where not exist (select 1 from current as c where c.code = n.code)

Answer 3

select * from new where name not in (select name from current) and code not in (select code from current);

would do the trick in mysql

Answer 4

Try this:

select * from new_table where name not in (select name from current_table) and
code not in (select code from current_table);

Answer 5

SELECT name, code 
FROM new n
WHERE NOT EXISTS(SELECT * FROM current c 
                 WHERE c.name = n.name OR c.code = n.code)