CODEWITHSUNDEEP
sqlsqlite
sober
sober

Reputation: 121

Find records on multiple fields not in another table

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

Upvotes: 3

Views: 6476

Answers (3)

Indra Prakash Tiwari
Indra Prakash Tiwari

Reputation: 1057

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.

Upvotes: 0

Keith Mifsud
Keith Mifsud

Reputation: 725

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)

Upvotes: 1

dnoeth
dnoeth

Reputation: 60482

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
 )

Upvotes: 12

Related Questions