What does a SELECT query using a LEFT JOIN followed by a comma separated table list mean?

Question

I came across this SQL Server SELECT query and I'm not understanding the LEFT JOIN is uses.

SELECT *
FROM project p
LEFT OUTER JOIN last_update lu ON lu.parent_table = 'project' AND lu.parent_id = p.record_id
LEFT OUTER JOIN project_final_report pfr ON pfr.project_id = p.record_id
LEFT OUTER JOIN logininfo logr ON logr.login_id = p.project_review_by
LEFT OUTER JOIN logininfo logfr ON logfr.login_id = pfr.last_updated_by
, community c, lookupvalue lv, lookupvalue d, lookupvalue r, lookupvalue su
, lookupvalue a, lookupvalue pc, lookupvalue pm, area_settings
WHERE p.record_id = 12345
AND p.data_system_type = 'P'
AND p.community_id = c.record_id
AND c.epa_region_id = lv.record_id
AND d.record_id = p.district_id
AND r.record_id = p.res_id
AND su.record_id = p.servunit_id
AND a.record_id = p.area_id
AND pc.record_id = p.project_category_id
AND pm.record_id = p.procurement_method_id
AND area_settings.area_id = p.area_id

You will notice the last LEFT JOIN is followed by a list of tables separated by commas. I'm familiar with LEFT JOINS, INNER JOINS and older school comma separated tables with WHERE clauses that turn them into INNER JOINS. But this mixing of LEFT JOIN with CS tables is new to me.

Can you explain how it equates to just LEFT AND INNER JOINS?

Answer 1

The comma is an obsolete join syntax equivalent to a CROSS JOIN. When coupled with an appropriate limiting condition in the WHERE clause, it is effectively an INNER JOIN.

The following is your query converted to use ANSI join syntax, where I have moved the appropriate limiting conditions to ON clauses.

SELECT *
FROM project p
LEFT OUTER JOIN last_update lu ON lu.parent_table = 'project' AND lu.parent_id = p.record_id
LEFT OUTER JOIN project_final_report pfr ON pfr.project_id = p.record_id
LEFT OUTER JOIN logininfo logr ON logr.login_id = p.project_review_by
LEFT OUTER JOIN logininfo logfr ON logfr.login_id = pfr.last_updated_by
JOIN community c ON c.record_id = p.community_id
JOIN lookupvalue lv ON lv.record_id = c.epa_region_id
JOIN lookupvalue d ON d.record_id = p.district_id
JOIN lookupvalue r ON r.record_id = p.res_id
JOIN lookupvalue su ON su.record_id = p.servunit_id
JOIN lookupvalue a ON a.record_id = p.area_id
JOIN lookupvalue pc ON pc.record_id = p.project_category_id
JOIN lookupvalue pm ON pm.record_id = p.procurement_method_id
JOIN area_settings ON area_settings.area_id = p.area_id
WHERE p.record_id = 12345
AND p.data_system_type = 'P'

The (subjectively less readable) direct translation of your query is:

SELECT *
FROM (
    project p
    LEFT OUTER JOIN last_update lu ON lu.parent_table = 'project' AND lu.parent_id = p.record_id
    LEFT OUTER JOIN project_final_report pfr ON pfr.project_id = p.record_id
    LEFT OUTER JOIN logininfo logr ON logr.login_id = p.project_review_by
    LEFT OUTER JOIN logininfo logfr ON logfr.login_id = pfr.last_updated_by
)
CROSS JOIN community c
CROSS JOIN lookupvalue lv
CROSS JOIN lookupvalue d
CROSS JOIN lookupvalue r
CROSS JOIN lookupvalue su
CROSS JOIN lookupvalue a
CROSS JOIN lookupvalue pc
CROSS JOIN lookupvalue pm
CROSS JOIN area_settings
WHERE p.record_id = 12345
AND p.data_system_type = 'P'
AND p.community_id = c.record_id
AND c.epa_region_id = lv.record_id
AND d.record_id = p.district_id
AND r.record_id = p.res_id
AND su.record_id = p.servunit_id
AND a.record_id = p.area_id
AND pc.record_id = p.project_category_id
AND pm.record_id = p.procurement_method_id
AND area_settings.area_id = p.area_id

(Note that the parenthesis above define join groupings and not a subselect.)