sql - 如果连接表不返回结果，则不返回任何行

Question

我现在正在使用多个内部联接，但其中一个表可能根本不包含任何结果。

以下是我目前正在使用的查询

SELECT
   property.id,
   full_address,
   street_address,
   street.street,
   city.city as city,
   state.state_code as state_code,
   zipcode.zipcode as zipcode, 
   property_history.date_event AS event_date,
   property_history.event AS event
FROM
    property 
    INNER JOIN street  ON street.id = property.street_id 
    INNER JOIN city    ON city.id = property.city_id
    INNER JOIN state   ON state.id = property.state_id
    INNER JOIN zipcode ON zipcode.id = property.zipcode_id
    INNER JOIN property_history ON property_history.property_id = property.id
WHERE
    full_address = ?

现在，如果property_history不返回任何行，那么我将一无所获。我假设我需要使用另一种类型的连接？

即使是空的，我仍然想获得property.id, full_address,等。street_addressproperty_history

标签： sqlpostgresql

Answer 1

你应该使用LEFT OUTER JOIN：

LEFT JOIN property_history ON property_history.property_id = property.id