A picture is worth more than a 1000 words, so here it goes. How could I craft a SQL statement to produce the table below?

In words, two different tables (Incomes and Expenses) share column names, but the row in each table is unrelated. In this case, an income transaction from a given user is unrelated to an expense transaction, even if it is from the same user.

I want to join these two tables into a single table such that all columns from both tables appear, but (1) the common table names are "merged" and (2) the unrelatedness of each row of data is preserved such that the distinct table names from one table are NULL when joined to a row from another table.

CodePudding user response：

I think what you are looking for is UNION query like below

select userid, username, incomeid, incomeamount, null as ExpenseID, null as expenseAmount
from table1
union
select userid, username, null as incomeid, null as incomeamount, null as ExpenseID, null as expenseAmount
from table2