查询中的查询:有更好的方法吗? [英] Queries within queries: Is there a better way?

问题描述

随着我构建更大、更高级的 Web 应用程序，我发现自己正在编写极其冗长和复杂的查询.我经常在查询中编写查询，因为我觉得从 PHP 调用一次数据库比调用多次并关联数据要好.

As I build bigger, more advanced web applications, I'm finding myself writing extremely long and complex queries. I tend to write queries within queries a lot because I feel making one call to the database from PHP is better than making several and correlating the data.

但是，任何了解 SQL 的人都知道 JOIN.就个人而言，我之前使用过一两个 JOIN，但是当我发现使用子查询时很快就停止了，因为我觉得编写和维护它更容易、更快捷.

However, anyone who knows anything about SQL knows about JOINs. Personally, I've used a JOIN or two before, but quickly stopped when I discovered using subqueries because it felt easier and quicker for me to write and maintain.

通常，我会执行可能包含来自相关表的一个或多个子查询的子查询.
考虑这个例子:

Commonly, I'll do subqueries that may contain one or more subqueries from relative tables.
Consider this example:

SELECT 
  (SELECT username FROM users WHERE records.user_id = user_id) AS username,
  (SELECT last_name||', '||first_name FROM users WHERE records.user_id = user_id) AS name,
  in_timestamp,
  out_timestamp
FROM records
ORDER BY in_timestamp

很少，我会在 WHERE 子句之后进行子查询.
考虑这个例子:

Rarely, I'll do subqueries after the WHERE clause.
Consider this example:

SELECT
  user_id,
  (SELECT name FROM organizations WHERE (SELECT organization FROM locations WHERE records.location = location_id) = organization_id) AS organization_name
FROM records
ORDER BY in_timestamp

在这两种情况下，如果我决定使用 JOIN 重写查询，我会看到任何改进吗?

In these two cases, would I see any sort of improvement if I decided to rewrite the queries using a JOIN?

作为一个笼统的问题，使用子查询或 JOIN 的优点/缺点是什么?一种方法比另一种更正确或被接受吗?

As more of a blanket question, what are the advantages/disadvantages of using subqueries or a JOIN? Is one way more correct or accepted than the other?

推荐答案

JOIN 优于分隔 [sub] 查询.
如果子选择(AKA 子查询)与外部查询不相关，则优化器很可能会扫描一次子选择中的表，因为该值不太可能改变.当您具有相关性时，就像提供的示例中一样，单次优化的可能性变得非常不可能.过去，人们认为相关的子查询执行 RBAR -- Row By Agonizing Row.使用 JOIN，可以在确保单次遍历表的同时实现相同的结果.

JOINs are preferable to separate [sub]queries.
If the subselect (AKA subquery) is not correlated to the outer query, it's very likely the optimizer will scan the table(s) in the subselect once because the value isn't likely to change. When you have correlation, like in the example provided, the likelihood of single pass optimization becomes very unlikely. In the past, it's been believed that correlated subqueries execute, RBAR -- Row By Agonizing Row. With a JOIN, the same result can be achieved while ensuring a single pass over the table.

这是对所提供查询的正确重写:

This is a proper re-write of the query provided:

   SELECT u.username,
          u.last_name||', '|| u.first_name AS name,
          r.in_timestamp,
          r.out_timestamp
     FROM RECORDS r 
LEFT JOIN USERS u ON u.user_id = r.user_id
 ORDER BY r.in_timestamp

...因为如果 USERS 表中不存在 user_id，则子选择可以返回 NULL.否则，您可以使用 INNER JOIN:

...because the subselect can return NULL if the user_id doesn't exist in the USERS table. Otherwise, you could use an INNER JOIN:

  SELECT u.username,
         u.last_name ||', '|| u.first_name AS name,
         r.in_timestamp,
         r.out_timestamp
    FROM RECORDS r 
    JOIN USERS u ON u.user_id = r.user_id
ORDER BY r.in_timestamp

派生表/内联视图也可以使用 JOIN 语法.

Derived tables/inline views are also possible using JOIN syntax.

这篇关于查询中的查询:有更好的方法吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！