CODEWITHSUNDEEP
sqlsqlitejoin
sirgeorge
sirgeorge

Reputation: 6541

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%' ))
    ....
));

Upvotes: 0

Views: 74

Answers (1)

NINtender
NINtender

Reputation: 109

Try something like this;

SELECT serial_nr, name, cert_type FROM certificates A 
inner join certificates B on A.serial_nr=B.serial_nr and B.name like'george%'
inner join ip_address C on A.serial_nr=C.serial_nr and C.Address like '192.168%'
...
WHERE A.cert_type<3

Upvotes: 1

Related Questions