How do I invert my join critera in TSQL?

Vote count:

0

I think this is a relatively basic operation in SQL, but I'm having a hard time figuring it out.

I have two tables, a source table I'm trying to SELECT my data from, and a reference table containing serial #'s and transaction #'s (the source table also has these columns, and more). I want to do two different SELECTs, one where the serial/trans number pair exist in the source table and one where the serial/trans number do not exist. (the combination of serial and trans number are the primary keys for both these tables)

Intially I'm doing this with a join like this:

SELECT * FROM source s
INNER JOIN reference r ON s.serial = r.serial AND s.trans = r.trans

I would think this should give me everything from the source table that has a serial/trans pair matching with one in the reference table. I'm not positive this is correct, but it is returning a reasonable number of results and I think it looks good.

When I go to do the opposite, get everything from source where the serial/trans pair do not match up with one in reference, I encounter a problem. I tried the following query:

SELECT * FROM source s
INNER JOIN reference r ON s.serial <> r.serial AND s.trans <> r.trans

When I run this the query goes on forever, it starts returning way more results than it should, more than are actually in the entire source table. It eventually ends with an OOM exception, I let it run for 20 min+. For some persepctive the source table I'm dealing with has about 13 million records, and reference table has about 105,000.

So how do I get the results I'm looking for? If it is not already clear, the number of results from the first query + results from second query should equal the total number of records in my source table.

Thanks for any help!

asked 6 mins ago

intA

305

2 Answers

Vote count:

1

SELECT * FROM source s
LEFT JOIN reference r ON s.serial = r.serial AND s.trans = r.trans
WHERE R.serial IS NULL 

answered 4 mins ago

AXMIM

95

Vote count:

1

I think you need something like NOT EXISTS:

SELECT * 
FROM source s
WHERE NOT EXISTS (SELECT 1
                  FROM reference r 
                  WHERE s.serial = r.serial AND s.trans = r.trans)

The above query will get everything from source where the serial/trans pair do not match up with one in reference.

As cited in comment from @Dan below NOT EXISTS generally has a performance advantage over LEFT JOIN in a situation like this.

edited 1 min ago

answered 3 mins ago

Giorgos Betsos

5,687

How do I invert my join critera in TSQL?