子查询是邪恶的吗? [英] Are subqueries evil?

问题描述

这个问题是在朋友的评论之后提出的.他说，当一个查询有很多子查询时，就表明数据库存在设计缺陷，必须避免.他还说，很多书都提出了相同的建议.

This question comes after a friend's comment. He said that when a query has a lot of subqueries, it's a signal that the database has design flaws and they must be avoided. He also said that many books suggest the same.

我部分同意，但我认为那些具有复杂逻辑的查询需要大量子查询，或者，为了避免子查询，查询的物化视图或大量数据冗余.

I agree in parts, but I think that are queries that have complex logic that a lot of subqueries are needed, or, to avoid the subqueries, a materialized view of a query or a lot of data redundancy.

那么，关于子查询的真相是什么?必须始终避免它们吗?他们没有问题吗?它们是否表明数据库设计缺陷?有没有可能有一个数据库设计，允许复杂的查询而没有数据冗余?

So, what is the truth about subqueries? Must they always be avoided? No problems with them? Do they indicate database design flaws? Is it possible to have a database design that allows complex queries without having data redundancy?

推荐答案

你朋友的逻辑有问题.

尽管 SQL 及其各种实现是基于关系模型的，但在某种程度上松散地基于关系模型，但它缺少许多基本关系运算符，特别是半连接、半差分(又名反连接)和除法.我经常使用子查询在 SQL 代码中编写半连接和半差异；至于除法，我不确定是否可以在不使用子查询的情况下在单个查询中执行！

Although SQL and its various implementations is based, somewhat loosely, on the relational model, it lacks keywords or shorthands for many basic relation operators, notably semi join, semi difference (a.k.a. anti join) and divide. I often write semi join and semi difference in SQL code using subqueries; as for divide, I'm not sure it is possible to perform in a single query without using subqueries!

所以我对子查询的使用是由 SQL 语言的可疑设计决定的，而不是我使用的数据库的设计.

So my use of subqueries is determined by the questionable design of the SQL language, rather than the design of the database I'm using.

附言我想知道您和/或您的朋友是否使用术语数据库"来表示数据库(数据集合)和 DBMS(管理数据的软件系统).如果是这样并且在上下文中您指的是 DBMS，那么当一个查询有很多子查询时，DBMS 有设计缺陷是一种‘气味’"这句话可能确实是正确的.

p.s. I wonder if you and/or your friend is using the term "database" to mean both database (the collection of data) and DBMS (the software system managing the data) interchangeably. If so and in context you mean DBMS then the statement "when a query has a lot of subqueries, it's a 'smell' that the DBMS has design flaws" may indeed be true.

这篇关于子查询是邪恶的吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！