-1

I have a database where I'm trying to find inconsistencies across two tables. I'm new to SQL and would like some help. The other questions I've looked up are confusing and too convoluted for what I'm trying to do.

Here's the situation: I have two tables where an ID is listed. I need to find the rows in each table for which there is no match in the other table. For example, table 1 would have a list of 1, 3, 4, 5, 7, 8, and 10. Table 2 would have a list of 1, 2, 4, 5, 6, 8, 9, and 10.I need to run a query to find the entries for 3, 6, and 9, since these are the entries that only appear in one of the two tables.

In my situation, it would be thousands of numbers. So, how would I go about finding the mismatches?

  • Are the ID's Primary Keys to the table, or are they stored values within the table itself? Also are you referencing MSSQL? ORACLE? MySQL? – Hector Jul 19 '16 at 12:53
  • 2
    It's your database. You really ought to be the one that knows what you are using. – Martin Smith Jul 19 '16 at 13:20
  • 1
    @Emanu; what does select @@version; show? – ypercubeᵀᴹ Jul 19 '16 at 13:22
  • 2
    It's not condescending in any way. If you ask for help in this site, you should at least know what DBMS you are using. – ypercubeᵀᴹ Jul 19 '16 at 13:30
  • 2
    @Emanu I understand. But the site is for professional help (from the Help section: "What topics can I ask about here? dba.se is for those needing expert answers to advanced database-related questions concerning traditional SQL RDBMS and NoSQL alternatives." If someone asks questions related to how to drive/turn/stop a truck, you assume they know what type/brand of truck it is even if they don't have a licence ;) – ypercubeᵀᴹ Jul 19 '16 at 13:47
4

MySQL (and the derivative) MariaDB have not implemented FULL JOIN. So you need either 2 queries or a UNION of 2 NOT EXISTS subqueries.

To find the not matched IDs using NOT EXISTS:

SELECT t1.id, 'IDs_in_t1_but_not_in_t2' AS mismatch
FROM table1 AS t1
WHERE NOT EXISTS
      ( SELECT *
        FROM table2 AS t2
        WHERE t2.id = t1.id
      ) 
UNION ALL
SELECT t2.id, 'IDs_in_t2_but_not_in_t1'
FROM table1 AS t2
WHERE NOT EXISTS
      ( SELECT *
        FROM table1 AS t1
        WHERE t1.id = t2.id
      ) ;

or using LEFT JOIN / IS NULL:

SELECT t1.id, 'IDs_in_t1_but_not_in_t2' AS mismatch
FROM table1 AS t1
  LEFT JOIN table2 AS t2
  ON t2.id = t1.id
WHERE t2.id IS NULL 
UNION ALL
SELECT t2.id, 'IDs_in_t2_but_not_in_t1'
FROM table1 AS t2
  LEFT JOIN table2 AS t1
  ON t1.id = t2.id
WHERE t1.id IS NULL ;
  • ypercube, here's what I got from your answer: Static analysis: 3 errors were found during analysis. Unrecognized keyword. (near "NOT" at position 78) Unrecognized keyword. (near "EXISTS" at position 82) Unexpected token. (near "(" at position 96) SQL query: Documentation SELECT t1.id, 'IDs_in_t1_but_not_in_t2' AS mismatch FROM table1 AS t1 WHERE NOT EXISTS ( LIMIT 0, 25 – Emanu Jul 19 '16 at 14:04
  • 2
    MariaDB has EXISTS just fine. It's the GUI tool you use that breaks things. – ypercubeᵀᴹ Jul 19 '16 at 14:07
  • 1
    Agree'd, MariaDB does have EXISTS – Hector Jul 19 '16 at 14:07
  • 1
    Or with LEFT JOIN + IS NULL. Although it's still madness that the query doesn't appear to be sent to the server as is (assuming there's no error on the OP's side). – Andriy M Jul 19 '16 at 14:08
  • 3
    I could write the LEFT JOIN / IS NULL version. But @Emanu, it's better to fix your phpmyAdmin. See this bug report: github.com/phpmyadmin/phpmyadmin/issues/11558 Seems like you need to update your phpmyadmin to the latest version. – ypercubeᵀᴹ Jul 19 '16 at 14:12
1

The question was only tagged sql when I answered. The following answer is valid for DBMS that support "Full outer join", such as SQL Server.

What you can use is a "full outer join". This join type will keep all values from the left table and all from the right table, and match those that match.

select t1.id, t2.id 
from t1 
full outer join t2 
on t1.id = t2.id
where t1.id is null or t2.id is null  

So (with simple example data):

create table #t1 (id int, val nvarchar(10))
create table #t2 (id int, val nvarchar(10))

insert into #t1 VALUES (1, 'one')
insert into #t1 VALUES (2, 'two')

insert into #t2 VALUES (2, 'two')
insert into #t2 VALUES (3, 'three')

select #t1.*, #t2.*
from #t1 
full outer join #t2 
on #t1.id = #t2.id

will yield the table

id   val  id   val
1    one  NULL NULL
2    two  2    two
NULL NULL 3    three

and

delete #t1
from #t1 
full outer join #t2 
on #t1.id = #t2.id
where #t2.id is null

will delete those entries from #t1 where there is no match in #t2

  • Thanks. I used your script, but after entering it, I get an error on line 3 stating "Unrecognized keyword. (near outer)" – Emanu Jul 19 '16 at 12:58
  • You need to replace the t1 and t2 alias with your actual database aliases. Send us your actual table names and column name for the ID and we can provide you code that you can copy and paste from. – Hector Jul 19 '16 at 13:00
  • I did. Here's the modded script you sent: – Emanu Jul 19 '16 at 13:04
  • select forest.GeoDataID, forest_patch.GeoDataID from forest full outer join forest_patch on forest.GeoDataID = forest_patch.GeoDataID where forest.GeoDataID is null or forest_patch.GeoDataID is null – Emanu Jul 19 '16 at 13:04
  • 1
    @Emanu Looks like you are using MySQL? That doesn't support full outer join – Martin Smith Jul 19 '16 at 13:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.