Don't return repeated ids and give priority to the second table-CodePudding

I have the following tables:

CREATE TABLE usuarios ( id INT, nome varchar );
INSERT INTO usuarios VALUES
(1, 'Teste'),
(2, 'Teste1'),
(3, 'Teste2'),
(4, 'Teste3'),
(5, 'Teste4'),

CREATE TABLE FichaColab( id INT, nomcompleto varchar );
INSERT INTO FichaColab VALUES
(1, 'Teste Teste'),
(3, 'Teste2 Teste2'),
(5, 'Teste4 Teste4'),

I intend to get all the names from the first table, but if the id exists in the second table, return the name of the second table instead of the name of the first. Here is the result I want:

id	nome
1	Teste Teste
2	Teste1
3	Teste2 Teste2
4	Teste3
5	Teste4 Teste4

I'm trying like this:

SELECT usuarios.id, usuarios.nome
FROM usuarios 
UNION 
SELECT FichaColab.Id, nomcompleto
FROM FichaColab

But this way returns everything from the two tables and repeats the ids and can't.

CodePudding user response：

You can use a LEFT JOIN between the two tables, then COALESCE on the name, by giving priority on the full name.

SELECT u.id, COALESCE(fc.nomcompleto, u.nome) AS nome
FROM      usuarios   u
LEFT JOIN FichaColab fc ON u.id = fc.id

Check the demo here.

CodePudding user response：

This should do the job:

select * from (
select FichaColab.* from FichaColab left join  usuarios on usuarios.id = FichaColab.id --everithing in table FichaColab 
union 
select usuarios.* from FichaColab right join usuarios on usuarios.id = FichaColab.id where FichaColab.id is null --everithing from usuarios where no record in FichaColab exists
) as unsorted
order by ID --sort it

Demo