英文:
Can't understand the mysql self left-join query
问题
以下是您要翻译的内容：
我遇到一个查询，其中包括以下内容，
SELECT it1.survey_set_id, it1.type, it1.value FROM survey_condition_filter it1 LEFT JOIN survey_condition_filter it2 ON(it1.survey_set_id = it2.survey_set_id AND it2.type = 3002) WHERE it1.type IN (2000, 2001, 2002) AND it2.value IS NULL;
为什么在上面的查询中使用了自连接左连接？
SELECT survey_set_id, type, value FROM survey_condition_filter WHERE type IN (2000, 2001, 2002);
上面的查询不等同于第一个使用了自连接左连接的查询吗？因为查询也只是过滤了 IN (2000, 2001, 2002) AND it2.value IS NULL。我对这里使用连接查询感到困惑，无法真正理解第一个查询的工作方式。
这是表格 survey_condition_filter：
+---------------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------------+--------------+------+-----+---------+-------+| survey_id | bigint | NO | PRI | NULL | || survey_set_id | bigint | NO | PRI | NULL | || type | int | NO | PRI | NULL | || condition | tinyint | NO | PRI | NULL | || value | varchar(15) | NO | PRI | NULL | || display_value | text | NO | | NULL | || order | int | YES | | NULL | || created_at | datetime | NO | | NULL | || created_by | varchar(255) | YES | | NULL | || updated_at | datetime | NO | | NULL | || updated_by | varchar(255) | YES | | NULL | || deleted_at | datetime | YES | | NULL | || deleted_by | varchar(255) | YES | | NULL | |+---------------+--------------+------+-----+---------+-------+
英文:
I come across a query which is,
SELECT it1.survey_set_id, it1.type, it1.value FROM survey_condition_filter it1 LEFT JOIN survey_condition_filter it2 ON(it1.survey_set_id = it2.survey_set_id AND it2.type = 3002) WHERE it1.type IN (2000, 2001, 2002) AND it2.value IS NULL;
Why is self left-join is used in the above query.
SELECT survey_set_id, type, value FROM survey_condition_filter WHERE type IN (2000, 2001, 2002);
isn't the above query is equivalent to the first query which used self left-join. since the query is just also filtering the IN (2000, 2001, 2002) AND it2.value IS NULL. I am confused the use of join query here and can't really understand the working of the first query.
This is table survey_condition_filter
+---------------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------------+--------------+------+-----+---------+-------+| survey_id | bigint | NO | PRI | NULL | || survey_set_id | bigint | NO | PRI | NULL | || type | int | NO | PRI | NULL | || condition | tinyint | NO | PRI | NULL | || value | varchar(15) | NO | PRI | NULL | || display_value | text | NO | | NULL | || order | int | YES | | NULL | || created_at | datetime | NO | | NULL | || created_by | varchar(255) | YES | | NULL | || updated_at | datetime | NO | | NULL | || updated_by | varchar(255) | YES | | NULL | || deleted_at | datetime | YES | | NULL | || deleted_by | varchar(255) | YES | | NULL | |+---------------+--------------+------+-----+---------+-------+
答案1
得分: 0
这是一种反连接。我们外连接一张表，然后仅保留外连接的行（通过在此应用 WHERE ... it2.value IS NULL）。在我看来，使用直接的 NOT EXISTS（或在许多情况下使用 NOT IN）更为常见，也更易读。
选择 it1.survey_set_id，it1.type，it1.value从 survey_condition_filter it1 中选择其中 it1.type 在 (2000、2001、2002) 中并且 NOT EXISTS(选择 null从 survey_condition_filter it2 中选择其中 it2.survey_set_id = it1.survey_set_id并且 it2.type = 3002);
此查询的作用很明显：选择所有类型为 2000/2001/2002 的行，其中同一 survey_set_id 不存在类型为 3002 的行。
如果 survey_set_id 是非空列，则可以使用 NOT IN，这样查询会变得稍微更短：
选择 it1.survey_set_id，it1.type，it1.value从 survey_condition_filter it1 中选择其中 it1.type 在 (2000、2001、2002) 中并且 it1.survey_set_id NOT IN(选择 it2.survey_set_id从 survey_condition_filter it2 中选择其中 it2.type = 3002);
英文:
This is an anti join. We outer join a table, but then only keep the outer joined rows (by applying WHERE ... it2.value IS NULL here). It is much more common - and much more readable in my opinion - to use a straight-forward NOT EXISTS (or NOT IN in many situations).
SELECT it1.survey_set_id, it1.type, it1.valueFROM survey_condition_filter it1WHERE it1.type IN (2000, 2001, 2002)AND NOT EXISTS(SELECT nullFROM survey_condition_filter it2WHERE it2.survey_set_id = it1.survey_set_idAND it2.type = 3002);
What this query does is obvious: select all type 2000/2001/2002 rows where not exists a 3002 row for the same survey_set_id .
If survey_set_id is a non-nullable column, you can use NOT IN, which gets the query even a tad shorter:
SELECT it1.survey_set_id, it1.type, it1.valueFROM survey_condition_filter it1WHERE it1.type IN (2000, 2001, 2002)AND itl.survey_set_id NOT IN(SELECT it2.survey_set_idFROM survey_condition_filter it2WHERE it2.type = 3002);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论