查询内的查询:有没有更好的方法? [英] 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-通过逐行行化.使用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，则subselect可以返回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屋！