What is the effect of using different COLLATE values in a WHERE clause?

I read the documentation 'Using COLLATE in SQL Statements' https://dev.mysql.com/doc/refman/8.0/en/charset-collate.html, but it doesn't say much about the effect of using different COLLATE values to the examples. Just as an example,
SELECT *
FROM t1
WHERE k LIKE _latin1 'Müller' COLLATE latin1_german2_ci;


What's the difference between using different values after COLLATE? What about if not using the COLLATE clause at all? How does one find the right or possible values to use after COLLATE?

it would make both sides have the same collation, when the databse can't compare the data and gives following error or similar ones

SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation '=' ....

And there is also to possibility to make a cs (case sensitive 'ABC is not eual to 'abc') to a ci case insensitive comparisons so 'ABC' would equal 'abc' or vice versa

StackExchange Database Administrators Q#336162, answer score: 2