How to get a SQL Query to combine two tables

Question

I have some questions about a SQL query. I've a table called CUSTOMER with the following fields (name, city):

Juan   New York
Louis  Madrid

And another table called ASESOR with the following fields (name, city):

Michael   New York
Peter  Zurich
Dan   Madrid

I need a query that combines both tables where the city is the same.

Expected result:

Juan New York
Loius Madrid
Michael New York
Dan Madrid

Peter has to be out of the results.

Thanks

Answer 1

You could use UNION ALL combined with EXISTS:

SELECT name , city
FROM CUSTOMER c
WHERE EXISTS (SELECT 1 FROM ASESOR a WHERE c.city= a.city)
UNION ALL
SELECT name , city
FROM ASESOR a
WHERE EXISTS (SELECT 1 FROM CUSTOMER c WHERE c.city= a.city);

DBFiddle Demo

Output:

┌──────────┬──────────┐
│  name    │   city   │
├──────────┼──────────┤
│ Juan     │ New York │
│ Louis    │ Madrid   │
│ Michael  │ New York │
│ Dan      │ Madrid   │
└──────────┴──────────┘