How to merge in SQL when the key variable is repeated in one of the tables?

I have a dataset with two tables. In the first, I have information about workers and in the second about companies. Each worker has an ‘id’ of the firm to which he belongs. I would like to create a new table from the merge of the worker base with the firm base, keeping the information from the two tables. The following is a minimum replicable example of the tables:

CREATE TABLE workers (id INT, name VARCHAR(100), sex VARCHAR(100), age VARCHAR(100));
INSERT INTO workers (id,name,sex,age)
VALUES (1,"W. White","male",54);
INSERT INTO workers (id,name,sex,age)
VALUES (2,"Hank Schroder","male",51);
INSERT INTO workers (id,name,sex,age)
VALUES (2,"Gus Fring","male",50);
INSERT INTO workers (id,name,sex,age)
VALUES (1,"Skyler","male",44);

CREATE TABLE firms (id INT, name VARCHAR(100), capital INT);
INSERT INTO firms (id,name,capital)
VALUES (1,"Bank Warburg",13051);
INSERT INTO firms (id,name,capital)
VALUES (2,"Atlas Security Information",42094);

I first tried to use the operator IN:

SELECT * FROM workers WHERE id IN (SELECT id FROM firms);

This command returns the desired structure, but without the data of the firms (i.e., name and capital):

1|W. White|male|54
2|Hank Schroder|male|51
2|Gus Fring|male|50
1|Skyler|male|44

I also tried:

MERGE firms AS TARGET
USING workers AS SOURCE 
ON (TARGET.id = SOURCE.id)

Which returns the error Error: near line 17: near" MERGE ": syntax error. As if sql is not recognizing the keyword “MERGE”.

How can I merge keeping company data?

Answer

You are misunderstanding the merge keyword, its purpose is for inserting and updating data from a source table against a target table, replacing the need for a seperate update where rows exist and insert where they don’t.

You don’t provide a basic example of your desired output, and you also state the query you tried gives you the expected output and at the same time is missing data… it cannot be both.

What you seem to want is just a simple join between these two tables, like so

select
    w.id, w.name, w.sex, w.age, 
    f.name FirmName, f.capital
from workers w
join firms f on f.id=w.id

If you want to create the result of this query as a new table, you can simply do

select
    w.id, w.name, w.sex, w.age, 
    f.name FirmName, f.capital
into <new table name>
from workers w
join firms f on f.id=w.id