mysql where not到left outer join

Question

我有以下查询，并希望将其转换为使用左外部连接，而不是not in，看看这样是否会运行得更快。目前在我们的数据库上运行这个查询大约需要40秒。我不太熟悉使用外部连接来转换这种类型的东西。

select
    c.contact_id as contact_id,
    c.orgid as organization_id,
    c.first_name as first_name,
    c.last_name as last_name,
    a.address_state as state
from cnx_contact as c
inner join cnx_address as a on c.home_address_uid = a.address_uid
where a.address_state = 'OH'
and (c.orgid = 45 or c.orgid = 55)
and c.contact_id NOT IN (
    select pc.contact_id
    from cnx_contact as c
    inner join cnx_address as a on c.home_address_uid = a.address_uid
    inner join cnx_contact_group_participant as gp on c.contact_id = gp.contact_id
    inner join cnx_contact_participant_role as cr on gp.participant_role_uid = cr.participant_role_uid
    inner join cnx_contact_group as cg on gp.group_uid = cg.group_uid
    inner join cnx_contact_group_participant as pgp on cg.primary_participant_uid = pgp.participant_uid
    inner join cnx_contact as pc on pgp.contact_id = pc.contact_id
    where (c.orgid = 45 or c.orgid = 55)
    and   cr.name = 'Applicant'
);

Answer 1

select
    c.columns
from cnx_contact as c
inner join cnx_address as a on c.home_address_uid = a.address_uid

LEFT JOIN 
(Subquery goes here) x
ON x.contact _id = c.contact_id
where a.participant_state = 'OH'
and c.orgid IN(45,55)
and x.contact_id IS NULL;