如何使用 INNER JOIN 将行添加到 SQL 查询的结果中？

Question

在过去，当我需要向 SQL 语句的结果中添加一行时，我会编写如下语句：

SELECT colA, colB FROM my_table   
UNION  
SELECT 'foo' AS colA, 'bar' as colB;

但是，假设我编写了以下 SQL：

SELECT t1.colA, t1.colB, t2.colC FROM my_table t1 INNER JOIN my_other_table t2

How can I add my extra row to my_table when it is INNER像这样加入到另一张桌子吗？

更新：哇，我刚才搞砸了。快到回家的时间了。我忘记了 where 子句！

SELECT t1.colA, t1.colB, t2.colC
FROM my_table t1
INNER JOIN my_other_table t2
  ON t1.colB = t2.colC

Answer 1

SELECT 
    (t1.colA, t1.colB FROM my_table 
     UNION 
     SELECT 'foo' AS colA, 'bar' as colB) as t1 
INNER JOIN 
    my_other_table t2 ON . . .

Answer 2

SELECT t1.colA, t1.colB, t2.colC FROM my_table t1 INNER JOIN my_other_table t2
UNION
SELECT 'foo' as colA, 'bar' as colB, 'baz' as colC

Answer 3

只需将第二个查询中的 mytable 替换为 (SELECT ...)，整个第一个查询（在括号中）：

SELECT t1.colA, t1.colB, t2.colC
FROM 
(   SELECT colA, colB
    FROM my_table
  UNION  
    SELECT 'foo' AS colA, 'bar' as colB
) AS t1
INNER JOIN my_other_table t2
  ON t1.colB = t2.colC     
;