how to show a specific value for a non existing value in second table in join? I have 2 tables, City(city_id, city_name) and count_staff_in_city(city_id, count_staff). the first table keeps the name of the city and its id, and the second table keeps the number of staffs working in each city. I want to join this 2 table in this way: if City.city_id exists in count_staff_in_city.city_id then show its value in select. if City.city_id does not exists in count_staff_in_city, then 0 should appear in output for this city.

how can I do this??
Use an outer join and replce NULLs using COALESCE:

SELECT C.city_name, COALESCE(CS.count_staff,0) AS count_staff
FROM City AS C 
LEFT OUTER JOIN count_staff_in_city AS CS ON C.city_id = CS.city_id;