如何在JOIN子句中使用SELECT语句 - How to use a SELECT statement into JOIN clause?

How to use a SELECT statement into JOIN clause?

本文关键字：SELECT 语句子句 JOIN | 更新日期: 2023-09-27

我有一个包含动态查询的变量。类似这样的东西：

$query = "SELECT id, subject FROM post1
          UNION ALL
          SELECT id, subject FROM post2
          UNION ALL
          SELECT id, subject FROM post3";

我还有这个问题：

SELECT code FROM mytable WHERE id = :id

现在我想用那个动态查询来join上面的查询。这是我的尝试：

SELECT t1.code t2.subject FROM mytable t1 
   LEFT JOIN ($query) t2 ON t1.col = t2.id
WHERE t1.id = :id
/*
SELECT t1.code t2.subject FROM mytable t1
   LEFT JOIN (SELECT id, subject FROM post1
               UNION ALL
              SELECT id, subject FROM post2
               UNION ALL
              SELECT id, subject FROM post3) t2 ON t1.col = t2.id
WHERE t1.id = :id
*/

它有效。但是对于庞大的数据来说需要花费大量的时间。我怎样才能让它更快？

SELECT t1.code t2.subject FROM mytable t1
   LEFT JOIN (SELECT id, subject FROM post1 
              JOIN mytable tt1 ON tt1.col = post1.id AND tt1.id=:id
               UNION ALL
              SELECT id, subject FROM post2
              JOIN mytable tt2 ON tt2.col = post2.id AND tt2.id=:id
               UNION ALL
              SELECT id, subject FROM post3
              JOIN mytable tt3 ON tt3.col = post3.id AND tt3.id=:id) t2 ON t1.col = t2.id
WHERE t1.id = :id

只需在内部查询4st中添加：id检查即可限制所选数据的数量。

您可以使用以下查询。

SELECT t1.code, t2.subject FROM (SELECT code, col FROM mytable WHERE id = :id ) t1 
 LEFT JOIN ($query) t2 ON t1.col = t2.id