Easy Way to get the Set Difference between datasets?

I have two SELECT queries returning datasets from two different tables that I know for a fact will share some id’s.

setA = system.db.runQuery(query1)
setB = system.db.runQuery(query2)

The first column of each set is an id. I want to know what id’s appear in setA that do not exist in setB. This way I can run insert statements for those specific Id’s and update statements for the rest (I am using info from setA to update setB later). Is there an easy way to do this or am I going to be stuck in loop hell?

If you put the ids from each dataset into a list, you could use set intersection() functions found here
So still a couple loops, but get the intersection pretty simply.
Though I do wonder why you don’t perform this at the database level with different joins?

Edit for Clarity:
I think the arguments intersection expects are called sets, you can find an example from the forum here

Well setA is the list of all current employees at a company and setB is how much each employee’s rate for a specific customer. Employees have left and been added, so some companies will not have currently active employees, or potentially have old employees who no longer work here. I need to modify setB’s rates. That is the rational I had for getting the current active employees first, seeing who I have to add and who I have to update. I couldn’t think of a way to do this strictly with joining but I am open to any ideas.

Something like this:

SELECT TableA.*
FROM TableA LEFT JOIN TableB ON TableA.ID=TableB.ID
WHERE TableB.ID IS NULL

1 Like