1

I'm not a SQL expert, but I'm able to write simple query but I have a problem with a query like:

SELECT field 
FROM TableA 
WHERE field NOT IN (SELECT field FROM TableB)

I have 2 table with the same field name, I'm sure there at least a record in TableA that is not in TableB but MySql returns 0 results.

The 2 field are Varchar64 , utf8_general_ci coded and contains values like '279960881351'.

I'm using PhpMyAdmin to run the query and the DB is hosted.

I wonder if there is something I do not know about MySql or PhpMyAdmin.

tadman
  • 208,517
  • 23
  • 234
  • 262
Daniele
  • 23
  • 4
  • 3
    "I'm sure there at least a record in TableA that is not in TableB" -- I think you're wrong. This simple query should work fine. – Barmar Aug 23 '23 at 15:30
  • Are the `field` columns indexed? Try repairing the indexes. – Barmar Aug 23 '23 at 15:31
  • The query looks just fine. Validate your assumptions once again, you may be wrong about them. Pick some concrete value and run a specialized query - with the chosen constant substituted. – Zazaeil Aug 23 '23 at 15:32
  • Could you try this instead, `SELECT FIELD FROM TableA MINUS SELECT FIELD FROM TableB` If this also doesn't provide you an answer your assumptions are not right my friend. – DataWrangler Aug 23 '23 at 15:34
  • @DataWrangler MySQL doesn't have `MINUS`. – Barmar Aug 23 '23 at 15:37
  • It works here: https://www.db-fiddle.com/f/nb4PWFfMEZQ8tXoSqWb91b/0 – Barmar Aug 23 '23 at 15:38
  • Check that the data does not have spaces or other invisible characters in it. The query is ok. – slaakso Aug 23 '23 at 15:38
  • The value I used above exists in TableA and not in TableB (at least a select does not return a record in TableB). The column is indexed (unique) in both tables and I drop and recreate both the indexes – Daniele Aug 23 '23 at 15:44
  • My link was wrong, maybe a value in table B is NULL : see [fiddle](https://www.db-fiddle.com/f/7hAcdjTPeZ961dXvnh2cWJ/0) – nfgl Aug 23 '23 at 15:46

1 Answers1

0

I'm really stupid! Thank you nfgl! There is at least 1 null in the table!

Daniele
  • 23
  • 4
  • 1
    And that's why you should use not exists rather than not in . See [here](https://www.db-fiddle.com/f/7hAcdjTPeZ961dXvnh2cWJ/0) – Ergest Basha Aug 23 '23 at 15:53