Joining and combining two tables that have the same ID for different rows

In SQLite I have two tables, tab_a and tab_b. Both have some similar columns and some that differ:

tab_a -> id | utc | name | data_1 | data_2 | data_3
tab_b -> id | utc | name | data_x | data_y | data_x

So in this example both tables have the columns id, utc, name and three other columns that are specific to those respective tables. Moreover, the id field in both tables can contain the same values. However, they are not the same data sets! So for example id = 1 in tab_a is another data set than id = 1 in tab_b – they just randomly have the same ID.

Now I want to (outer?) join these two tables to get a combined list of all records from both of them, of course only with the shared columns. But since the IDs of both tables can be the same, I need an additional column in my join to tell what table this row comes from.

So for example the joined result could look like this:

id | tab   | utc  | name
---+-------+------+-----
 1 | tab_a | ...  | ...
 2 | tab_a | ...  | ...
 2 | tab_b | ...  | ...
 3 | tab_b | ...  | ...
 4 | tab_a | ...  | ...
 4 | tab_b | ...  | ...

How can I join two tables in SQLite this way and add this “custom” column? If that is not possible, what other “best practice” approach should I use in my case?

Answer

You have to use UNION, not JOIN for your purposes like this:

CREATE TABLE tab_a (
    id int PRIMARY KEY,
    utc text,
    name text
);
CREATE TABLE tab_b (
    id int PRIMARY KEY,
    utc text,
    name text
);

INSERT INTO tab_a (id, utc, name)
VALUES (1, "utc1", "name1"), (2, "utc2", "name2");

INSERT INTO tab_b (id, utc, name)
VALUES (1, "utc3", "name3"), (2, "utc4", "name4");

SELECT id, name, utc, "tab_a" as tab from tab_a
UNION SELECT id, name, utc, "tab_b" as tab from tab_b;

Response:

id	name	utc	tab
1	name1	utc1	tab_a
1	name3	utc3	tab_b
2	name2	utc2	tab_a
2	name4	utc4	tab_b

Example in DB Fiddle