In MySQL WHERE clause, how can I check for each item in a comma separated list against another comma separated list that may be ordered differently

You could use like, the % are wildcards.

select *
from events
where courses like "%ABC123%" or courses like "%XYZ345%"

There are other approaches: https://dev.mysql.com/doc/refman/8.0/en/pattern-matching.html

It might also be worth normalising the data so that each value is stored in its own row in another table rather than in a comma separated string, as using like will start to hurt performance as the table grows.

@user3783243 mentions loose matching which is a good point and just one reason why storing data like this isn’t the best approach.

One way around it would be to tweak the query above to something like:

select *
from events
where courses like "%ABC123,%" or courses like "%XYZ345,%"

but this poses another problem, are the comma separated values always split by a single comma (ABC123,XYZ345) or is there any whitespace (ABC123, XYZ345).

Another problem pointed out by @GarethD is that the previous approaches won’t match the last value of the comma separated string (unless it does have a trailing comma). One way I can think of is to do something like this, but it starts making the query a bit clunky, and also assumes all values are 6 characters in length, at this point, it might be worth using a regular expression.

select *
from events
where courses like "%ABC123,%" or courses like "%XYZ345,%"
or right(courses, 6) = 'ABC123' or right(courses, 6) = 'XYZ345'

If all values are indeed six characters then it might be worth trying other mysql functions such as locate, substring and regexp to try and simplify the query.