基于if else statemnet的postgres内连接

Question

我遇到了这样一种情况:如果一个标志为true，那么内部连接一个表，否则就不需要连接。

SELECT     ti.upc, 
              ti.quantity, 
              ti.return_type, 
              ti.amount, 
              ti.transaction_date, 
              ti.transaction_time, 
              ti.location_id
   FROM       transaction_item ti 
   IF _user = true THEN
   INNER JOIN user_location ul 
   ON         ul.location_id=ti.location_id 
   AND      ul.user_id=_user_id 
   AND        ul.location_id=_location_id
   END IF; 
   WHERE        ti.transaction_date BETWEEN _start_date::date AND  _end_date::date
   AND        ti.upc IS NOT NULL as ti

Answer 1

您可以将inner join更改为left join，并将对该变量的检查添加到on条件。然后，where子句可以过滤出应该进行连接但没有成功的行：

SELECT     
    ti.upc, 
    ti.quantity, 
    ti.return_type, 
    ti.amount, 
    ti.transaction_date, 
    ti.transaction_time, 
    ti.location_id
FROM transaction_item ti 
LEFT JOIN user_location ul 
    ON  _user = true
    AND ul.location_id=ti.location_id 
    AND ul.user_id=_user_id 
    AND ul.location_id=_location_id
WHERE    
    ti.transaction_date BETWEEN _start_date::date AND  _end_date::date
    AND ti.upc IS NOT NULL
    AND NOT (_user = true AND ul.location_id IS NULL)

Answer 2

您没有使用来自user_location的任何数据，因此我建议您：

SELECT ti.upc, ti.quantity, ti.return_type,  ti.amount, 
       ti.transaction_date, ti.transaction_time, ti.location_id
FROM ransaction_item ti 
WHERE (_user <> true OR
       EXISTS (SELECT 1
               FROM user_location ul 
               WHERE ul.location_id = ti.location_id AND
                     ul.user_id = _user_id AND
                     ul.location_id = _location_id
              )
      ) AND
      ti.transaction_date BETWEEN _start_date::date AND _end_date::date AND
      ti.upc IS NOT NULL;

对我来说，“条件连接”似乎很难理解。但是，WHERE子句中的条件过滤器非常容易遵循。