Use Merge or List. Contains: Best option?

Hi filiparibeiro ,

I think this depends on where your two tables come from and how much flexibility you need on the output format/structure.

If they're both from the same SQL Server DB (or other foldable source) and you need to view the output in a number of different ways then I'd recommend merging, providing you keep query folding intact. I'd recommend this as, depending on what you're trying to match and how you want the results to be output, the different merge types (LEFT OUTER, FULL OUTER etc.) give you the most flexibility over this output.

If one is from an SQL Server DB (or other foldable source) and the other any other source, but you just need to see a basic list of items from one table that are/are not in the other, then I'd recommend using List.Contains on a buffered list (from the non-foldable source), providing you keep query folding intact on the foldabe source. I'd recommend this as using this method will stream the buffered list to the foldable source in the WHERE clause so can crunch massive tables super-fast, but at the expense of being limited on the type of output you can achieve at these high speeds.

If neither source is foldable, I'd probably go for List.Contains with a buffered list. This will only dump your match list into memory, rather than scanning an entire table for the merge.

Pete

Ok, so, let's say you have TableA with a list of ID's and you want to check if they are in TableB. You would create the following step in TableB to filter it for only ID's that are in TableA:

Table.SelectRows(
    previousStepName,
    each List.Contains(
        List.Buffer(TableA[ID]),
        TableB[ID]
    )
)

Non-foldable TableB: This puts the TableA[ID] list into memory before checking against TableB to prevent multiple scans of TableA

Foldable TableB: This streams the TableA[ID] list into the WHERE clause of the TableB native query to pass the work back to the source.

Pete