SQL 左联接与常规联接相结合

Question

我有以下查询连接了一堆表。

我想从 INDUSTRY 表中获取每条具有 consolidated_industry_id = 1 的记录，无论它是否与其他表匹配。 我相信这需要通过左连接来完成？

SELECT attr.industry_id        AS option_id,
       attr.industry           AS option_name,
       uj.ft_job_industry_id,
       Avg(CASE
             WHEN s.salary > 0 THEN s.salary
           END)                AS average,
       Count(CASE
               WHEN s.salary > 0 THEN attr.industry
             END)              AS count_non_zero,
       Count(attr.industry_id) AS count_total
FROM   industry attr,
       user_job_ft_job uj,
       salary_ft_job s,
       user_job_ft_job ut,
       [user] u,
       user_education_mba_school mba
WHERE  u.user_id = uj.user_id
       AND u.user_id = ut.user_id
       AND u.user_id = mba.user_id
       AND uj.ft_job_industry_id = attr.industry_id
       AND uj.user_job_ft_job_id = s.user_job_id
       AND u.include_in_student_site_results = 1
       AND u.site_instance_id IN ( 1 )
       AND uj.job_type_id = 1
       AND attr.consolidated_industry_id = 1
       AND mba.mba_graduation_year_id NOT IN ( 8, 9 )
       AND uj.admin_approved = 1
GROUP  BY attr.industry_id,
          attr.industry,
          uj.ft_job_industry_id 

这只返回一行，但行业表中有 8 个匹配项，其中 consolidated_industry_id = 1。

---编辑：这里真正的问题是，如何将左联接与常规联接相结合？

Answer 1

对可能缺少相应记录的表使用 left join。把每个表的条件放在join的on子句中，而不是放在where中，因为这实际上会使它们成为内连接。像这样：

select
  attr.industry_id AS option_id, attr.industry AS option_name,
  uj.ft_job_industry_id, AVG(CASE WHEN s.salary > 0 THEN s.salary END) AS average, 
  COUNT(CASE WHEN s.salary > 0 THEN attr.industry END) as count_non_zero,
  COUNT(attr.industry_id) as count_total 
from
  industry attr
  left join user_job_ft_job uj on uj.ft_job_industry_id = attr.industry_id and uj.job_type_id = 1 and uj.admin_approved = 1
  left join salary_ft_job s on uj.user_job_ft_job_id = s.user_job_id 
  left join [user] u on u.user_id = uj.user_id and u.include_in_student_site_results = 1 and u.site_instance_id IN (1)
  left join user_job_ft_job ut on u.user_id = ut.user_id
  left join user_education_mba_school mba on u.user_id = mba.user_id and mba.mba_graduation_year_id not in (8, 9)
where
  attr.consolidated_industry_id = 1 
group by
  attr.industry_id, attr.industry, uj.ft_job_industry_id

如果您有任何知道总是有相应记录的表，只需为此使用 innser join。