Table 1 contains a unique ID column with millions of rows.
Table 2 contains 2 columns, matchId1, matchId2 which can hold an ID from Table 1. There can be many rows in Table 2 referencing a given ID.
How can I list the IDs from Table 1 that are not contained in Table 2 (in either column) in an efficient way?
SELECT x.*
FROM table1 x
LEFT
JOIN table2 y
ON y.id = x.id
WHERE y.id IS NULL;
or, more specifically...
SELECT x.*
FROM table1 x
LEFT
JOIN table2 y
ON x.id IN(y.id1,y.id2)
WHERE y.id IS NULL;
This assumes that there is a single column PK on table2
A couple of suggestions
Using jons
SELECT x.*
FROM table1 x
LEFT JOIN table2 y ON y.matchId1 = x.id
LEFT JOIN table2 z ON z.matchId2 = x.id
WHERE y.matchId1 IS NULL AND z.matchId2 IS NULL
Using IN
SELECT x.*
FROM table1 x
WHERE x.id NOT IN
(
SELECT matchId1 FROM table2
UNION
SELECT matchId2 FROM table2
)
Try this:
SELECT table1.*
FROM table1
LEFT JOIN Table2 on table1.id in (table2.matchId1,table2.matchId2)
WHERE table2.matchId1 IS NULL AND table2.matchId2 IS NULL
