将SQL WHERE IN转换为JOIN [英] Convert SQL WHERE IN to JOIN

问题描述

我有一个数据库，存储有关虚构人物的各种信息.有一张桌子上的人，具有一般信息，例如姓名，地址等，还有一些更具体的桌子，每个人都有健康史和接受过的教育. 我现在想做的是，基于相似之处，例如在同一所学校同一时间，同一位医生或同一时间在同一家医院接受治疗，为一个人建立可能的联系.

I have a database storing various information about fictional people. There is a table person with general information, such as name, adress etc and some more specific tables holding health history and education for everyone. What I'm trying to do now, is getting possible connections for one person based on similarities like being at the same school for the same time or having the same doctor or being treated in the same hospital at the same time.

以下查询对此非常有用(:id是有问题的人的ID)，但是它却非常慢(大约需要6秒钟才能得到结果).

Following Query works fine for this (:id being the id of the person in question), however it is horribly slow (takes about 6secs to get a result).

SELECT person.p_id as id, fname, lname, image FROM person WHERE 
                (person.p_id IN (
                    SELECT patient from health_case WHERE 
                        doctor IN (SELECT doctor FROM health_case WHERE patient =:id ) 
                        OR center IN (SELECT hc2.center FROM health_case as hc1, health_case as hc2 WHERE hc1.patient = :id AND hc2.center = hc1.center AND (hc1.start <= hc2.end AND hc1.end >= hc2.start)))
                OR person.p_id IN (
                    SELECT ed2.pupil FROM education as ed1, education as ed2 WHERE 
                        ed1.school IN (SELECT school FROM education WHERE pupil = :id) AND ed2.school = ed1.school AND (ed2.start <= ed1.end AND ed2.end >= ed1.start)
                )) 
                AND person.p_id != :id

将其转换为使用JOIN子句的最佳方法是什么?我似乎不知所措……

What would be the best approach to convert it to use JOIN clauses? I somehow seem unable to wrap my head around these...

推荐答案

我认为我了解您要执行的操作.剥皮猫的方法有多种，但是我建议将查询分为两个单独的查询，然后用几个内部联接替换复杂的WHERE子句吗?所以，像这样:

I think I understand what you're trying to do. There is more than one way to skin a cat, but may I suggest splitting your query into two separate queries, and then replacing the complicated WHERE clause with a couple inner joins? So, something like this:

/* Find connections based on health care */
SELECT p2.p_id as id, p2.fname, p2.lname, p2.image
FROM person p
JOIN health_case hc on hc.patient = p.p_id
JOIN health_case hc2 on hc2.doctor = hc.doctor and hc2.healthcenter = hc.healthcenter and hc.start <= hc2.end and hc.end >= hc2.start and hc2.patient <> hc.patient
JOIN person p2 on p2.p_id = hc2.patient and p2.p_id <> p.p_id
WHERE p.p_id = :id

然后，创建一个单独的查询以根据教育程度获得联系:

Then, create a separate query to get connections based on education:

/* Find connections based on education */
SELECT p2.p_id as id, p2.fname, p2.lname, p2.image
FROM person p
JOIN education e on e.pupil = p.p_id
JOIN education e2 on e2.school = e.school and e2.start <= e.end AND e2.end >= e.start and e.pupil <> e2.pupil
JOIN person p2 on p2.p_id = e2.pupil and p2.p_id <> p.p_id
WHERE p.p_id = :id

如果您确实希望合并数据结果，则可以使用UNION，因为两个查询都从人员表中返回相同的列.

If you really want the data results to be combined, you can use UNION since both queries return the same columns from the person table.

这篇关于将SQL WHERE IN转换为JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！