I recently had a project where I needed import a very large number of records from one database to another. All of the records are unique and in order to move them as quickly as possible, a left join was needed to make sure that the data didn't get imported twice.
This table had about 6 NOT NULL fields and the unique constraint was on the combination of those fields. Now it was possible to join straight from one of the tables to the other using those fields in the JOIN like
LEFT JOIN table A
ON B.field1 = A.field1
AND B.field2 = A.field2
However joining on multiple fields is rather slow. Because the combination of fields is unique, what I did in order to speed this up was to create a match field that held the combined string of all the other fields into one.
UPDATE table A
SET matchfield = CONCAT(field1,field2)
WHERE matchfield IS NULL;
That enabled me to join both tables on a single field, which in turn made the join and then the import much faster.
LEFT JOIN table A
ON B.matchfield = A.matchfield
The one caveat here is that if the match field is out of date, then the joins will not work correctly and you will get duplicate field errors on insert or data will not be inserted because the match field says it already was.