SQL with several joins

Question

Is this query acceptable in terms of performance (I get the correct data) or can it be optimized?

SELECT
    bankInstitution.name,
    person.firstName,
    person.surname,
    offer.offerId,
    bank.personId,
    bank.bankOtherName,
    bank.sortCode,
    bank.number,
    loan.loanId
    offer.campaignId,
FROM bank
    JOIN loan ON bank.personId = loan.personId AND bank.isCurrent = 1
    JOIN offer ON loan.loanId = offer.loanId
    JOIN person ON person.personId = bank.personId
    JOIN bankInstitution ON bankInstitution.bankInstitutionId = bank.bankInstitutionId
WHERE offer.CampaignId = 1 AND offer.Processed Is NULL

Answer 1

There is an illegal comma before FROM.

I would put bank.isCurrent = 1 in the WHERE-clause instead of as a join condition.

For consistency, I would reverse the equalities in the join conditions:

• JOIN loan ON loan.personId = bank.personId
• JOIN offer on offer.loanId = loan.loanId

… to match …

• JOIN person ON person.personId = bank.personId
• JOIN bankInstitution ON bankInstitution.bankInstitutionId = bank.bankInstitutionId

None of the above affects performance. What you have is a very ordinary joining of tables, and your query expresses it in the usual way. There's not much more that can be done for performance by tweaking the query.

What you should do, though, is ensure that all of the joins are being performed using indexes, not full-table scans. You can verify that by prepending ANALYZE to your SELECT, which will give you the query execution plan. If any of the joins is being performed with a full-table scan, create an index on the relevant column. If your schema is properly defined, with the PRIMARY KEYs and FOREIGN KEYs declared, then you should already be fine, I think.