0
194
ViewsSQL Join table from 1 of 2 different tables optimized search
As a simplification, I have four tables, book, publisher, author, and company. Both book and publisher have a reference to author, but that reference can be null.
I can find the author by joining the author table using an OR join query like this, but it is incredibly slow.
edit I have to use the author table to join another.
Schema:
select book.title,
author.name,
company.name as company_name
from book
inner join publisher on book.publisher_id = publisher.id
inner join author
on (book.author_id = author.id OR publisher.author_id = author.id)
inner join company on author.company_id = company.id;
I am wondering what the best way to optimize this query would be, instead of joining on an OR conditional. Thanks
1 answers
Answer question0
You are correct. OR in the ON clause can really slow down a query.
Instead, use two left joins:
select b.title, coalesce(ab.name, ap.name) as name
from book b inner join
publisher p
on b.publisher_id = p.id left join
author ab
on b.author_id = ab.id left join
author ap
on p.author_id = ap.id;
Formally, you might need to add where ab.id is not null or ap.id is not null if you only want to return rows with a match in one of the tables.