How to merge two tables?

Question

Oracle

In general, the problem is this, I have two tables, let's say table1, table2, in the second table, in addition to all the data, there is a column with statuses that can take two values ​​\u200b\u200bof 1 or 2, I connect these tables, provided that records with status 1 do not fall, i.e. where status <> 1, but this selection is intermediate, i.e. this selection will participate in other decisions where id with status 1 is needed, I have such a question, is it possible to make it so that when joining these tables with where <> 1, but to have one more column where those id with where status = 1 will be stored?

Answer 1

The problem contradicts itself. Either make two selections, or where you already need to check for status

Answer 2

Create a view in which both tables are joined without any status restrictions, and then refer to it in other queries. Where necessary - filter, where not necessary - do not filter.

Answer 3

with 
t1 as
(
select .... from table1 where .....
),
t2 as
(
select .... from table2 where .....
)

select ...... 
from t1
join t2 ....

Answer 4

Can. But you need to form a pseudo-column.
Nozzi has already set one syntax, you can use it. but new chips don't always match old bases. And it’s not convenient to write a lot on what I’m typing on. Therefore, I will try to explain using the old syntax

Select cast(0 as int) pseudoid, id, <все другие нужные столбцы> from t1 where status <> 1
Union
Select cast(0 as int) pseudoid, id, <все другие нужные столбцы> from t2 where status <> 1
Union

-- теперь самый фокус
Select  id, 0, <все другие нужные столбцы> from t1 where status = 1

What to look for:
1. In a union, the names and types of columns are given by their first select.
2. The number of columns and their types of all selects must match. Otherwise, you need to do the casting.
3. Sorting is set in the last select and applied to the entire union.