如何在Teradata SQL中基于另外两个表中现有的一些值创建具有二进制值的表

Question

我在Teradata SQL中有两个表，如下所示：

表1：

ID
10
11
12

Teradata似乎在本机上支持足够的ISO SQL-2003，因此不需要Teradata特定的SQL扩展或专有功能(即，完全相同的查询将在MSSQL Server、Oracle、MariaDB等中工作(。

您将需要table1和table2的值的UNION，然后返回JOINed，这很简单：

WITH distinctIdValues AS (
SELECT id FROM table1
UNION
SELECT id FROM table2
)
SELECT
dv.id,
CASE WHEN t1.id IS NOT NULL THEN 1 ELSE 0 END AS tab1,
CASE WHEN t2.id IS NOT NULL THEN 1 ELSE 0 END AS tab2
FROM
distinctIdValues AS dv
LEFT OUTER JOIN table1 AS t1 ON dv.id = t1.id
LEFT OUTER JOIN table2 AS t2 ON dv.id = t2.id

然后，您可以将此查询用作VIEW或将其具体化为新的TABLE:

CREATE VIEW foobar AS /* same SQL as above */;
SELECT * FROM foobar;

Teradata的文档不清楚CTE如何/是否可以与INSERT语句一起使用，所以我将使用内部查询：

CREATE TABLE foobar (
id   int     NOT NULL PRIMARY KEY,
tab1 byteint NOT NULL,
tab2 byteint NOT NULL
);
INSERT INTO foobar ( id, tab1, tab2 )
SELECT
dv.id,
CASE WHEN t1.id IS NOT NULL THEN 1 ELSE 0 END AS tab1,
CASE WHEN t2.id IS NOT NULL THEN 1 ELSE 0 END AS tab2
FROM
(
SELECT id FROM table1
UNION
SELECT id FROM table2
)
AS dv
LEFT OUTER JOIN table1 AS t1 ON dv.id = t1.id
LEFT OUTER JOIN table2 AS t2 ON dv.id = t2.id
ORDER BY
dv.id
; 

---

或者仅此：

道具@dnoeth提醒我它可以简化为：

SELECT
COALESCE( t1.id, t2.id ) AS id,
CASE WHEN t1.id IS NULL THEN 0 ELSE 1 END AS tab1,
CASE WHEN t2.id IS NULL THEN 0 ELSE 1 END AS tab2
FROM
table1 AS t1
FULL OUTER JOIN table2 AS t2 ON t1.id = t2.id
ORDER BY
COALESCE( t1.id, t2.id )

您只需要一个完整的外部联接：

SELECT
Coalesce(t1.id, t2.id) AS id
,CASE WHEN t1.id IS NULL THEN 0 ELSE 1 END AS tab1
,CASE WHEN t2.id IS NULL THEN 0 ELSE 1 END AS tab2
FROM table1 AS t1 
FULL JOIN table2 AS t2 
ON t1.id = t1.id