List of elements in join layer without a match

Question

When I set up a table join in one layer A, how do I find all elements from layer B that didn't find their way into the joined table?

This is much like doing a left outer join and trying to find all elements from the right table (B above) that didn't go into the join.

I suppose I should be able to set up a join in layer B and use layer A as the lookup table. Then, I could just mark all elements with NULL values in the joined fields, but somehow, QGIS won't let me set up a join in layer B for layer A. Which is really strange.

---

Layer A has 5000+ features. Layer B has 280 features.

When I inspect the attribute table for layer A and sort layer A by an identifier of layer B, I get 268 joined elements. OK, I have 12 elements in layer B without a match. Of course, since they don't appear in the joined attribute table, I would need to look for them in layer B. But in layer B, of course, they are unmarked.

Setting up a join in layer B properties for layer A doesn't work (the join settings window remains blank after setting up the join). Well, then I tried "join by attribute" and did not skip the faulty output, which I saved as separate layer. This layer should have 12 elements, but only has 10. I must be doing something wrong. How would you find the unjoinable features in layer B?

Answer 1

I would go the pure SQL way :)

You can do an SQL left join using "Virtual Layers" and filter it instead of trying to use the "Joins" from the layer.

Always with "Virtual Layers", you can also do a "normal" join and then use EXCEPT to get records not matching (see https://www.sqlitetutorial.net/sqlite-except/). If you need to find out only common records ids, you can look at INTERSECT operation e.g https://www.sqlitetutorial.net/sqlite-intersect/