How to create table with binary values based on existing some values in two other tables in Teradata-CodePudding

I have two tables in Teradata SQL like below:

Table1:

ID
10
11
12

Table2:

ID
10
13
14
15

Based on two tables above I need to create one table like below. So:

col: tab1 --> If ID is in table 1 give them 1 and 0 otherwise.
col: tab2 --> If ID is in table 2 give them 1 and 0 otherwise.

Desired result:

ID	tab1	tab2
10	1	1
11	1	0
12	1	0
13	0	1
14	0	1
15	0	1

How can I do that in Teradata SQL ?

CodePudding user response：

Teradata seems to support enough of ISO SQL-2003 natively, so no Teradata-specific SQL extensions or proprietary features is needed (i.e. the exact same query will work in MSSQL Server, Oracle, MariaDB, etc).

You'll want a UNION of table1 and table2's values and then JOINed back, which is straightforward:

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

You can then use this query either as a VIEW or materialize it into a new TABLE:

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

SELECT * FROM foobar;

Teradata's documentation is unclear about how/if a CTE can be used with an INSERT statement, so I'll use an inner-query instead:

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
;

Or just this:

Props to @dnoeth for reminding me that it can be reduced to this:

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 )

CodePudding user response：

You just need a Full Outer Join:

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