-1

I have a table A with one column named a, and a file "test.txt" contains:

111111AAAA
222222BBBB
3333DDDDDD
.....

The records in test.txt have the same type with "a" column. How to select all from A except the records in "test.txt"?

Update: I tried 3 ways and the results not equal. What a strange!

// 7073 records -- Using NOT IN
SELECT * from mt_users WHERE TERMINAL_NUMBER_1 NOT IN (SELECT TERMINAL_NUMBER FROM A);

// 7075 records -- Using NOT EXISTS
SELECT * from mt_users WHERE NOT EXISTS (SELECT 1 FROM A WHERE A.TERMINAL_NUMBER = mt_users.TERMINAL_NUMBER_1);

// 7075 records -- Using LEFT JOIN
SELECT * FROM mt_users m LEFT JOIN A a ON m.TERMINAL_NUMBER_1 = a.TERMINAL_NUMBER WHERE a.TERMINAL_NUMBER IS NULL;
emeraldhieu
  • 9,380
  • 19
  • 81
  • 139

3 Answers3

1

Step 1. Put the records from test.txt into a different table.

Step 2.

SELECT a from tableA WHERE a NOT EXISTS (SELECT a FROM newTable)
aF.
  • 64,980
  • 43
  • 135
  • 198
1

doing what aF wrote would be my first answer too. if you cant/do not want to do that try "NOT IN" like:

SELECT a FROM A WHERE a NOT IN(...)

You have to generate the content of the () in the code where you create your query

Flo
  • 1,660
  • 4
  • 21
  • 34
1

Firstly put all records from file into the newTable and make sure that there are no additional spaces at the beginning or the end in each field.

select a from tableA t where not exists(select 1 from newTable n where n.a = t.a)
Michał Powaga
  • 22,561
  • 8
  • 51
  • 62