Rewrite SQL query using joins (or make it more efficient some other way)

Is it possible to rewrite the following SQL query using JOIN(s) or make it in other way more efficient ? I may be using SQLite (I can connect to few different databases), which means I cannot use RIGHT JOIN(s). The ellipsis (...) in the query below means that I can have many UNION(s) there.

I construct this query programmatically, because there is no other way in my case. I'm just trying to get the idea how I could rewrite it to make it more efficient. Any ideas ? Thanks in advance.

SELECT serial_nr, name, cert_type FROM certificates WHERE (cert_type<3 AND
serial_nr IN
(
    SELECT DISTINCT serial_nr FROM certificates WHERE (cert_type<3 AND (name LIKE 'george%' ))
    UNION
    SELECT DISTINCT serial_nr FROM ip_addresses WHERE ((address LIKE '192.168%' ))
    ....
));