CODEWITHSUNDEEP
mysqlsql
Tanya von Degurechaff
Tanya von Degurechaff

Reputation: 173

Union All while prioritizing the first table if the second table have same column value

I have 2 tables with similar column, and i want to join them so i can sort them out together.

select * from (
        (select * from table_1) 
        union all
        (select * from table_2 )
       ) t order by id column_1;

table_1

And

table_2

Result

result

How do I show table_1 row only when there's already a duplicate in table_2, like this ?

result_2

Upvotes: 0

Views: 1165

Answers (1)

forpas
forpas

Reputation: 164089

You can use not exists in the where clause when you select from table_2:

select * from table_1
union all
select t2.* from table_2 t2
where not exists (select 1 from table_1 t1 where t1.colum_1 = t2.colum_1)
order by colum_1

There is no need for subqueries.
The order by clause operates on the result of the union.

See the demo.
Results:

> colum_1 | column_2
> :------ | -------:
> A       |        1
> B       |        1
> C       |        1
> D       |        2
> E       |        2

Upvotes: 1

Related Questions