title: Joining a table, but ensuring all rows from first table are returned
body: <p>I have a system for storing postal information in PostgreSQL. We have a <code>postal_carriers</code> table, that details carrier information. We have a <code>postal_zones</code> table that holds zones for <code>postal_carriers</code>. We have a <code>countries</code> table holding country information. I've now created a <code>postal_zone_mapping</code> table, that maps countries to zones of different carriers. Here is the definition for this.</p>
  <pre><code>CREATE TABLE shopman.postal_zone_mapping
  (
      postal_zone_id integer,
      postal_carrier_id integer,
      country_id integer,
      CONSTRAINT pzm_key UNIQUE (country_id, postal_carrier_id),
      CONSTRAINT pzm_fkey FOREIGN KEY (country_id)
          REFERENCES shopman.countries (country_id) MATCH SIMPLE
          ON UPDATE NO ACTION
          ON DELETE NO ACTION,
      CONSTRAINT postal_zone_mapping_postal_carrier_id_fkey FOREIGN KEY (postal_carrier_id)
          REFERENCES shopman.postal_carriers (postal_carrier_id) MATCH SIMPLE
          ON UPDATE NO ACTION
          ON DELETE NO ACTION,
      CONSTRAINT postal_zone_mapping_postal_zone_id_fkey FOREIGN KEY (postal_zone_id)
          REFERENCES shopman.postal_zones (postal_zone_id) MATCH SIMPLE
          ON UPDATE NO ACTION
          ON DELETE NO ACTION
  )
  </code></pre>
  <p>This is the statement I am using to select the data, selecting the <code>country</code> table with a left join on the <code>postal_zone_mapping</code> table, as I want all the countries listed for a carrier, whether they're mapped to a particular carrier and zone or not.</p>
  <pre><code>    SELECT c.country_id,
             COALESCE(postal_zone_id,-1) AS postal_zone_id,
             COALESCE(postal_carrier_id,-1) AS postal_carrier_id,
             country_name
        FROM shopman.countries c 
             LEFT JOIN shopman.postal_zone_mapping p 
                    ON c.country_id=p.country_id
       WHERE postal_carrier_id=1 
          OR postal_carrier_id IS NULL 
       ORDER BY country_name ASC 
      OFFSET 0 ROWS FETCH FIRST 50 ROWS ONLY
  </code></pre>
  <p>I want this query to return every country, a carrier ID if it matches the one we want along with zone id, but with my query, if we insert a map for a carrier, then the results returned do not have this country. Where am I going wrong with my query? Also, is there a better way to achieve this mapping? I went down this route to avoid having to dynamically add columns or have arrays.</p>
  <p>Many thanks</p>
  <p>For reference, here is the definition of the other tables:</p>
  <pre><code>CREATE TABLE shopman.postal_carriers
  (
      postal_carrier_id serial,
      postal_carrier_name text,
      description text,
      CONSTRAINT postal_carriers_pkey PRIMARY KEY (postal_carrier_id),
      CONSTRAINT postal_carriers_postal_carrier_name_key UNIQUE (postal_carrier_name)
  )
  
  CREATE TABLE shopman.postal_zones
  (
      postal_zone_id serial,
      postal_carrier_id integer,
      postal_zone_name text,
      CONSTRAINT postal_zones_pkey PRIMARY KEY (postal_zone_id),
      CONSTRAINT postal_zones_postal_zones_name_key UNIQUE (postal_carrier_id, postal_zone_name),
      CONSTRAINT postal_zones_postal_carrier_id_fkey FOREIGN KEY (postal_carrier_id)
          REFERENCES shopman.postal_carriers (postal_carrier_id) MATCH SIMPLE
          ON UPDATE NO ACTION
          ON DELETE NO ACTION
  )
  
  CREATE TABLE shopman.countries
  (
      country_id serial,
      country_name text,
      code_2 text&quot;,
      code_3 text,
      region_id integer,
      CONSTRAINT countries_pkey PRIMARY KEY (country_id),
      CONSTRAINT countries_code_2_key UNIQUE (code_2),
      CONSTRAINT countries_code_3_key UNIQUE (code_3),
      CONSTRAINT countries_name_key UNIQUE (country_name),
      CONSTRAINT countries_region_id_fkey FOREIGN KEY (region_id)
          REFERENCES shopman.regions (region_id) MATCH SIMPLE
          ON UPDATE NO ACTION
          ON DELETE NO ACTION
  )
  </code></pre>