In the query the 'Field3' value gets replaced with the value within that column, for each row. So its checking if the value of the Field3 column matches any of those 3 strings and if so the row gets selected.
The join is just a python function on the String type (as you can see I'm defining a string inline: ', ' then calling the function .join() on it), concatenating the 3 strings from the list together, separated by a comma and space into a single string. This has to be done because the query is a single string.
If you used
fieldValue = '%45B%', '%46A%', '%47C%'
It will work as well, but will give you a Tuple instead of a list. This works fine in the current situation, but if you had a script with variable fieldValues, a Tuple wouldn't work because the values within are not mutable - this is why I suggested the list.
Does that clear it up?