Find records on multiple fields not in another table

Question

Let's say I have 2 tables (tb1, tb2) with both the following schema:

CREATE TABLE tb1 (
  col1 INT NOT NULL,
  col2 TEXT NOT NULL,
  col3 TEXT NOT NULL,
  col4 REAL
);

How do I find records of tb1 which are not present in tb2 on columns col1, col2, col3?

I researched on this, this and this but so far they're all finding records only on one column. I've also used the codes/logic in these links but ended up returning the wrong result with really bad performance (45K records on tb1, 1.7M records on tb2). I'm trying to implement this on SQLite.

If you wanna see, here's my sample code (using left join w/ where is null), but don't rely on it:

SELECT *
FROM tb1
LEFT JOIN tb2
ON
tb1.col1 = tb2.col1 AND
tb1.col2 = tb2.col2 AND
tb1.col3 = tb2.col3
WHERE
tb2.col1 IS NULL AND
tb2.col2 IS NULL AND
tb2.col3 IS NULL

I have a JOIN for 7 columns and your sample code worked. I also tried with `IS NULL OR` condition, it works even then. I don't know why though. — scientific_explorer, Feb 23 '19 at 12:37

score 12 · Accepted Answer · answered May 18 '15 at 07:10

12

Try NOT EXISTS instead, of course performance might depend on existing indexes...

SELECT *
FROM tb1
WHERE NOT EXISTS
 ( 
   SELECT *
   FROM tb2
   WHERE
      tb1.col1 = tb2.col1 AND
      tb1.col2 = tb2.col2 AND
      tb1.col3 = tb2.col3
 )

answered May 18 '15 at 07:10

dnoeth

59,503
4
39
56

I have a JOIN for 7 columns and the sample code provided by OP worked. I also tried with IS NULL OR condition, it works even then. I don't know why though. I tried your approach but it took really long for me. My tb1 has 5M records and tb2 has 300k records. – scientific_explorer Feb 23 '19 at 18:20

score 1 · Answer 2 · edited May 18 '15 at 07:21

1

How about something like:

SELECT *
FROM tb1
WHERE NOT EXISTS (SELECT * FROM tb2
                  WHERE tb1.col1 = tb2.col1
                    AND tb1.col2 = tb2.col2
                    AND tb1.col3 = tb2.col3)

edited May 18 '15 at 07:21

jarlh

42,561
8
45
63

answered May 18 '15 at 07:13

Keith Mifsud

725
5
16

2

exactly as mentioned by @dnoeth – Keith Mifsud May 18 '15 at 07:13
@dnoeth, not anymore! – jarlh May 18 '15 at 07:21

score 0 · Answer 3 · answered May 18 '15 at 07:35

0

Hi @Dnoeth and @Sober,

You might encounter with following prob with Dnoeth solution, let me know if you are not.

Msg 402, Level 16, State 1, Line 9 The data types text and text are incompatible in the equal to operator.

note: i could not post this in comment because of less reputation.

answered May 18 '15 at 07:35

Indra Prakash Tiwari

947
1
6
15

I didn't encounter any errors; in fact, @dnoeth's answer performed well (with the index). – sober May 18 '15 at 07:48

Find records on multiple fields not in another table

3 Answers3

Linked

Related