Concatenate two sql tables

Question

I have two tables (Table 1 and Table 2 in the following example) with two columns each. What I am trying to do is:

keep all the entries of Table 1
add the entries from Table 2 if the element of col_3 (in Table 2) is also an element of col_1 (in Table 1)
the Result Table will have three columns: the two original ones from Table 1, and col_4 from Table 2

Table 1

col_1	col_2
1	a
1	b
2	c
3	d

Table 2

col_3	col_4
1	w
1	x
2	y
4	z

Result Table

col_1	col_2	col_4
1	a	NULL
1	b	NULL
2	c	NULL
3	d	NULL
NULL	NULL	w
NULL	NULL	x
NULL	NULL	y

In the example, all the elements of Table 1 are in the Result Table (and populate col_1 and col_2), while only the first three elements of Table 2 are in the Result Table (and populate col_4).

This looks very similar to a left join in some sense. But instead of just creating a new column and adding the values to it, how do I create the new rows and fill them with the entries from Table 2? Maybe some combination of union and join might work.

I would just select both tables select a.col1, a.col2, b.col4 from table1 a, table2 b where b.col3 = a.col1 — Danimal, Aug 16 '22 at 14:14
@lemon the actual query I am using is massive, but I guess the simplified version according to the my example above would be something like: SELECT t1.col_1, t1.col_2, t2.col_4 FROM t1 LEFT JOIN t2 ON t2.col_3=t1.col_1 ... however this is not exactly what I am looking for — Fraccalo, Aug 16 '22 at 14:43
This is not clear. "keep all the entries of Table 1" & "add the entries from Table 2" but put them together how exactly? Your prose doesn't even mention nulls. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. Where are you stuck with what relevant parts? [mre] [ask] [Help] PS Please clarify via edits, not comments. — philipxy, Aug 16 '22 at 14:43
@philipxy Apologies, I thought the table example where clear enough, and I found quite hard to explain it in words (I actually tried, but it got messier and messier as I was writing). I'll try to amend the text to make it more clear — Fraccalo, Aug 16 '22 at 14:45
Notice I just told you how to make it clear. [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) PS It is not productive to hope people will generalize from one example. PS If you can't clearly write what you want in words, how to you expect to reason about, code, research or request a solution? (Rhetorical.) (But as I said I just told you how to express in a way that allows that.) PS Here "the business situation" is the degenerate one where t1 is rows where (c1,c2) IN t1 & t2 is rows where (c3,c4) IN t2. — philipxy, Aug 16 '22 at 15:02

score 1 · Accepted Answer · answered Aug 16 '22 at 14:43

You can solve this with a UNION:

SELECT col_1, col_2, NULL as col_3 FROM table1
UNION
SELECT NULL, NULL, col_3 FROM table2 
WHERE col_3 in (SELECT DISTINCT col_1 FROM table1)

Despite the initial reaction to use a join, you are actually stacking these tables/result-sets on top of each other, therefore a UNION is more appropriate.

score 0 · Answer 2 · answered Aug 16 '22 at 14:48

0

I'd say a UNION ALL is more appropriate as it avoids the performance penalty of checking and removing duplicates. There should be no duplicates because you're merging 3 distinct attributes from 3 different tables.

answered Aug 16 '22 at 14:48

planetmatt

407
3
10

Concatenate two sql tables

2 Answers2