Mysql子选择替代 [英] Mysql subselect alternative

问题描述

我有一个查询，我知道可以使用子选择来完成，但是由于表大小很大(每个表 100k+ 行)，我想找到使用连接的替代方法.这不是一个家庭作业问题，但用这样的术语分享一个例子更容易.

I have a query that I know can be done using a subselect, but due to large table sizes (100k+ rows per table) I would like to find an alternative using a join. This is not a homework question, but it's easier to share an example in such terms.

假设有两个表:
学生
:id :name
1   汤姆
2   莎莉
3   本

Suppose there are two tables:
Students
:id :name
1   Tom
2   Sally
3   Ben

书籍
:id  :student_id :book
1               数学101
2                历史
3                NULL
4   3           数学101

Books
:id  :student_id  :book
1    1                  Math 101
2    1                  History
3    2                  NULL
4    3                  Math 101

我想找到所有没有历史书的学生.工作子选择是:
从没有 id 的学生中选择姓名(从 book = 'History' 的书籍中选择 student_id)；

I want to find all students who don't have a history book. Working subselect is:
select name from students where id not in (select student_id from books where book = 'History');

这会返回 Sally 和 Ben.

This returns Sally and Ben.

感谢您的回复！

推荐答案

是性能问题吗?或者这只是一些避免子查询的理论(家庭作业?)问题?如果是性能，那么这个:

Is performance the problem? Or is this just some theoretical (homework?) question to avoid a subquery? If it's performance then this:

SELECT *
FROM studnets s
WHERE NOT EXISTS
(SELECT id FROM books WHERE student_id = s.id AND book = 'History')

将比您在 MySQL 上执行的 IN 执行得更好(在某些其他数据库上，它们的性能相同).这也可以改写为连接:

will perform a lot better than the IN you're doing on MySQL (on some other databases, they will perform equivalently). This can also be rephrased as a join:

SELECT s.*
FROM studnets s
LEFT JOIN books b ON s.id = b.student_id AND b.book = 'History'
WHERE b.id IS NULL

这篇关于Mysql子选择替代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！