Joins with multiple conditions (matching fields)

[kannes]

Feature description

Currently, if one wants to join two layers in the UI of the layer properties, one can only select a single field on each layer to evaluate for matching values.

This is fine and easy if you have unique IDs. But it does not support joining of data that needs multiple fields to match.

E. g. to join two layers that have address data in multiple fields like "city", "street", "housenumber", one has to first create (virtual) fields on both layers and then use those two fields for the join. This is not easily discoverable for newcomers, not super easy and potentially slow.

In SQL speak: Currently one can only use one condition in a JOIN ("JOIN b ON a.field = b.field"), not multiple conditions ("JOIN b ON a.field_1 = b.field_1 AND a.field_2 = b.field_2").

It would be great if we could specify multiple fields to match per JOIN (for the same joined layer).

Additional context

I am surprised but could not find a prior feature request for this, sorry if I missed it. #29846 is related as it would allow the use of expressions to calculate new values on the fly but that's something for advanced users and potentially slow in comparison.

[EnFiore]

I have the same issue. This problem force to duplicate layer and it is no much efficiently. Similar request in #46131