This query mixes SQL-89 and SQL-92 join syntax. There are some very
unintuitive rules that come into play when that happens, so the safest
thing to do, generally, is to pick one syntax and stick with it. If you
need outer joins, that means using the SQL-92 syntax.

Here's the original:

select <blah> from INSPECTIONS, PROPERTIES,
PROPERTY_OWNERS
left join OWNERS on (OWNERS.ENTITY_ID = PROPERTY_OWNERS.ENTITY_ID),
PROPERTY_BUYERS
left join BUYERS on (BUYERS.ENTITY_ID = PROPERTY_BUYERS.ENTITY_ID),
PROPERTY_AGENTS
left join AGENTS on (AGENTS.ENTITY_ID = PROPERTY_AGENTS.ENTITY_ID)
left join COMPANIES on (COMPANIES.ID = AGENTS.COMPANY_ID)
where INSPECTIONS.INSPECTION_ID = 23 and
INSPECTIONS.SERVICE_TYPE = 1 and
PROPERTIES.ID = INSPECTIONS.PROPERTY_ID and
PROPERTY_OWNERS.PROPERTY_ID = PROPERTIES.ID and
PROPERTY_BUYERS.PROPERTY_ID = PROPERTIES.ID and
PROPERTY_AGENTS.PROPERTY_ID = PROPERTIES.ID

Here's a rough translation.

select <blah> from
INSPECTIONS
join PROPERTIES on (PROPERTIES.ID = INSPECTIONS.PROPERTY_ID)
join PROPERTY_OWNERS on (PROPERTY_OWNERS.PROPERTY_ID = PROPERTIES.ID)
join PROPERTY_BUYERS on (PROPERTY_BUYERS.PROPERTY_ID = PROPERTIES.ID)
join PROPERTY_AGENTS on (PROPERTY_AGENTS.PROPERTY_ID = PROPERTIES.ID)
left join OWNERS on (OWNERS.ENTITY_ID = PROPERTY_OWNERS.ENTITY_ID)
left join BUYERS on (BUYERS.ENTITY_ID = PROPERTY_BUYERS.ENTITY_ID)
left join AGENTS on (AGENTS.ENTITY_ID = PROPERTY_AGENTS.ENTITY_ID)
left join COMPANIES on (COMPANIES.ID = AGENTS.COMPANY_ID)
where INSPECTIONS.INSPECTION_ID = 23 and
INSPECTIONS.SERVICE_TYPE = 1


Your original turns into a three-way sort merge join and probably gets an
unexpected results. The single syntax version should produce a primary
four way inner join on indexed terms with four ancillary lookups. I once
had both the knowledge and patience to interpret the mixed syntax, but have
lost one, the other, or both.

Regards,


Ann

BTW, this is also acceptable, and possibly more clear.

select <blah> from
INSPECTIONS
join PROPERTIES on (PROPERTIES.ID = INSPECTIONS.PROPERTY_ID)
join PROPERTY_OWNERS on (PROPERTY_OWNERS.PROPERTY_ID = PROPERTIES.ID)
left join OWNERS on (OWNERS.ENTITY_ID = PROPERTY_OWNERS.ENTITY_ID)
join PROPERTY_BUYERS on (PROPERTY_BUYERS.PROPERTY_ID = PROPERTIES.ID)
left join BUYERS on (BUYERS.ENTITY_ID = PROPERTY_BUYERS.ENTITY_ID)
join PROPERTY_AGENTS on (PROPERTY_AGENTS.PROPERTY_ID = PROPERTIES.ID)
left join AGENTS on (AGENTS.ENTITY_ID = PROPERTY_AGENTS.ENTITY_ID)
left join COMPANIES on (COMPANIES.ID = AGENTS.COMPANY_ID)
where INSPECTIONS.INSPECTION_ID = 23 and
INSPECTIONS.SERVICE_TYPE = 1