SQL查询结果，其中Join具有NULL结果(SQL Query Results where Join has NULL results)

我想知道你是否可以提供帮助 - 我已经创建了一个SQL查询（SQLServer），虽然有些结果没有显示，我认为它是由于某些连接上的NULL结果。例如，我知道一些连接将使用NULL结果，因为100.resp_unit_no可能是NULL以及100.employee_no。

到目前为止的查询是：

select 058.DESCRIPTION "IRU", 111.job_ref "Job Reference", 117.DESCRIPTION "Status", 108.STATUS "Fixed?", 105.DESCRIPTION "Job Type", 111.POSTCODE "Postcode", 111.site_id "Site ID", 108.CONTRACT_START "Contract Earliest", 108.CONTRACT_COMPLETION "Contract Latest", 108.BOOKED_DURATION "Planned Duration (mins)", 111.IMPORTANCE "Importance", 111.CUSTOMER_NAME "Customer Name", 100.expected_start "Expected Start", 100.expected_end "Expected End", 210.NAME "Employee", 111.ON_HOLD "On Hold?" FROM 111_JOBS 111, 108_JOB_DETAILS 108, 058_RESPONSIBILITY_UNITS 058, 100_ACTIVITIES 100, 105_ACTIVITY_TYPES 105, 210_EMPLOYEES 210, 117_STATUS_DESCRIPTIONS 117 where 111.JOB_NO=108.JOB_NO AND 111.JOB_NO=100.JOB_NO AND 100.RESP_UNIT_NO=058.RESP_UNIT_NO AND 111.JOB_TYPE=105.ACTIVITY_TYPE AND 210.EMPLOYEE_NO=100.EMPLOYEE_NO AND 117.STATUS=100.ACTIVITY_STATUS order by IRU, Employee, EXPECTED_START;

我想我需要一个正确的外连接，但我不能让结构正确...

任何帮助将不胜感激！

I'm wondering if you can help - I've got an SQL Query (SQLServer) which i've created, although some results are not being shown and i think its due to NULL results on some joins. For example, i know some of the joins will be using NULL results as 100.resp_unit_no may well be NULL aswell as 100.employee_no.

The query so far is:

select 058.DESCRIPTION "IRU", 111.job_ref "Job Reference", 117.DESCRIPTION "Status", 108.STATUS "Fixed?", 105.DESCRIPTION "Job Type", 111.POSTCODE "Postcode", 111.site_id "Site ID", 108.CONTRACT_START "Contract Earliest", 108.CONTRACT_COMPLETION "Contract Latest", 108.BOOKED_DURATION "Planned Duration (mins)", 111.IMPORTANCE "Importance", 111.CUSTOMER_NAME "Customer Name", 100.expected_start "Expected Start", 100.expected_end "Expected End", 210.NAME "Employee", 111.ON_HOLD "On Hold?" FROM 111_JOBS 111, 108_JOB_DETAILS 108, 058_RESPONSIBILITY_UNITS 058, 100_ACTIVITIES 100, 105_ACTIVITY_TYPES 105, 210_EMPLOYEES 210, 117_STATUS_DESCRIPTIONS 117 where 111.JOB_NO=108.JOB_NO AND 111.JOB_NO=100.JOB_NO AND 100.RESP_UNIT_NO=058.RESP_UNIT_NO AND 111.JOB_TYPE=105.ACTIVITY_TYPE AND 210.EMPLOYEE_NO=100.EMPLOYEE_NO AND 117.STATUS=100.ACTIVITY_STATUS order by IRU, Employee, EXPECTED_START;

I think i need a right outer join, but i cant quite get the structure correct...

Any help would be greatly appreciated!

最满意答案

如果您更改查询以使用ANSI-92显式连接语法而不是您正在使用的ANSI-89语法（正如名称建议超过20年的日期），那么您的查询将变得更容易操作：

SELECT 058.DESCRIPTION "IRU", 111.job_ref "Job Reference", 117.DESCRIPTION "Status", 108.STATUS "Fixed?", 105.DESCRIPTION "Job Type", 111.POSTCODE "Postcode", 111.site_id "Site ID", 108.CONTRACT_START "Contract Earliest", 108.CONTRACT_COMPLETION "Contract Latest", 108.BOOKED_DURATION "Planned Duration (mins)", 111.IMPORTANCE "Importance", 111.CUSTOMER_NAME "Customer Name", 100.expected_start "Expected Start", 100.expected_end "Expected End", 210.NAME "Employee", 111.ON_HOLD "On Hold?" FROM 111_JOBS 111 INNER JOIN 108_JOB_DETAILS 108 ON 111.JOB_NO = 108.JOB_NO INNER JOIN 100_ACTIVITIES 100 ON 111.JOB_NO = 100.JOB_NO LEFT JOIN 058_RESPONSIBILITY_UNITS 058 100.RESP_UNIT_NO = 058.RESP_UNIT_NO LEFT JOIN 105_ACTIVITY_TYPES 105 ON 111.JOB_TYPE = 105.ACTIVITY_TYPE LEFT JOIN 210_EMPLOYEES 210 ON 210.EMPLOYEE_NO = 100.EMPLOYEE_NO LEFT JOIN 117_STATUS_DESCRIPTIONS 117 ON 117.STATUS = 100.ACTIVITY_STATUS ORDER BY IRU, Employee, EXPECTED_START;

现在，在不知道哪些列可能为null的情况下，我不知道哪个连接要从INNER更改为LEFT ，所以我只更改了其中的一些，但是您应该能够根据需要更改一些。

Aaron Bertrand写了一篇很好的文章，更有理由转向更新的连接语法。

If you change your query to use ANSI-92 explicit join syntax rather than the ANSI-89 syntax that you are using (which is, as the names suggest over 20 years out of dates) then you query will become much easier to manipulate:

SELECT 058.DESCRIPTION "IRU", 111.job_ref "Job Reference", 117.DESCRIPTION "Status", 108.STATUS "Fixed?", 105.DESCRIPTION "Job Type", 111.POSTCODE "Postcode", 111.site_id "Site ID", 108.CONTRACT_START "Contract Earliest", 108.CONTRACT_COMPLETION "Contract Latest", 108.BOOKED_DURATION "Planned Duration (mins)", 111.IMPORTANCE "Importance", 111.CUSTOMER_NAME "Customer Name", 100.expected_start "Expected Start", 100.expected_end "Expected End", 210.NAME "Employee", 111.ON_HOLD "On Hold?" FROM 111_JOBS 111 INNER JOIN 108_JOB_DETAILS 108 ON 111.JOB_NO = 108.JOB_NO INNER JOIN 100_ACTIVITIES 100 ON 111.JOB_NO = 100.JOB_NO LEFT JOIN 058_RESPONSIBILITY_UNITS 058 100.RESP_UNIT_NO = 058.RESP_UNIT_NO LEFT JOIN 105_ACTIVITY_TYPES 105 ON 111.JOB_TYPE = 105.ACTIVITY_TYPE LEFT JOIN 210_EMPLOYEES 210 ON 210.EMPLOYEE_NO = 100.EMPLOYEE_NO LEFT JOIN 117_STATUS_DESCRIPTIONS 117 ON 117.STATUS = 100.ACTIVITY_STATUS ORDER BY IRU, Employee, EXPECTED_START;

Now, without knowing what columns may be null, I don't know which joins to change from INNER to LEFT, so I have only changed a few of them, but you should be able to change a few more as needed.

Aaron Bertrand has written a good article, with even more reasons to switch to the newer join syntax.

更多推荐