SQL Server Cannot Query NULL or Empty Values in a Field or column in a table

bgtovc5b 于 2023-02-18 发布在其他

关注(0)|答案(1)|浏览(185)

I have a query that is 99% working, but I cannot query NULL or empty records for

dbo.HUR_STAFF_DEMOGRAPHICS.DEMOGRAPHICS_POSITION = '' OR 
dbo.HUR_STAFF_DEMOGRAPHICS.DEMOGRAPHICS_POSITION IS NULL

I tried both, but the SQL statement below returns no rows if I add the lines above. However it DOES query rows for ALL staff that have a location and or position entered into the database, but my goal is to query this information for all staff that have a location and or position in the database, or if have a location and or position is NULL or BLANK (empty).

I queried the Demographics table alone, and I get a total of 1120 records, I also calculated the columns that have BLANK or NULL (so no position title or Location) and that is 316 records.

Sure enough, when I run this query I get 804 records returned, which means the query is not querying Null or empty columns for Location or Position Title .

Can anyone see why the query will not return the null columns?

SELECT DISTINCT 
    dbo.HUR_STAFF_DEMOGRAPHICS.employee_no,
    first_name, last_name, PREFERRED_NAME,
    location,
    DEMOGRAPHICS_POSITION,
    EMPLOYEE_EMAIL,
    Location_name,
    Description
FROM
    dbo.HUR_STAFF_DEMOGRAPHICS,
    dbo.HUR_STAFF_INTERLINK,
    dbo.HUR_LOCATION,
    dbo.HUR_POSITION_CODE,
    dbo.HUR_EMAIL_ADDRESS
WHERE  
    dbo.HUR_STAFF_DEMOGRAPHICS.employee_no = dbo.HUR_STAFF_INTERLINK.employee_no
    AND dbo.HUR_STAFF_DEMOGRAPHICS.location = dbo.HUR_LOCATION.Location_no
    AND dbo.HUR_STAFF_DEMOGRAPHICS.DEMOGRAPHICS_POSITION = dbo.HUR_POSITION_CODE.POSITION_CODE
    AND dbo.HUR_STAFF_DEMOGRAPHICS.EMPLOYEE_NO = dbo.HUR_EMAIL_ADDRESS.EMPLOYEE_NO
    AND dbo.HUR_STAFF_DEMOGRAPHICS.employee_status = 'A'
    AND dbo.HUR_STAFF_DEMOGRAPHICS.DEMOGRAPHICS_POSITION IS NULL
ORDER BY 
    last_name

Thanks in advance for your time and expertise.

sql-server

来源：https://stackoverflow.com/questions/75466249/cannot-query-null-or-empty-values-in-a-field-or-column-in-a-table

1条答案

按热度按时间

3htmauhk1#

I believe your issue is that you are joining to the DEMOGRAPHICS_POSITION table on DEMOGRAPHICS_POSITION = POSITION_CODE , but are also looking for DEMOGRAPHICS_POSITION = '' or DEMOGRAPHICS_POSITION = NULL values. Assuming that the DEMOGRAPHICS_POSITION table has no empty code entry, the join to that table will fail for both of these conditions and no record will be returned.

The solution is to change that join to a LEFT JOIN , but the query must be first rewritten to use ANSI join syntax.

Using table aliases is also a good practice to simplify the statement and improve readability.

Try:

SELECT DISTINCT D.employee_no,
                first_name,
                last_name,
                PREFERRED_NAME,
                location,
                DEMOGRAPHICS_POSITION,
                EMPLOYEE_EMAIL,
                Location_name,
                Description
FROM HUR_STAFF_DEMOGRAPHICS D
JOIN HUR_STAFF_INTERLINK I
    ON D.employee_no = I.employee_no
JOIN HUR_LOCATION L
    ON D.location = L.Location_no
LEFT JOIN HUR_POSITION_CODE P
    ON D.DEMOGRAPHICS_POSITION = P.POSITION_CODE
JOIN HUR_EMAIL_ADDRESS E
    ON D.EMPLOYEE_NO = E.EMPLOYEE_NO
WHERE D.employee_status = 'A'
AND (D.DEMOGRAPHICS_POSITION = '' OR D.DEMOGRAPHICS_POSITION IS NULL)
ORDER  BY last_name

Alternately, for this particular usage, you can drop the HUR_POSITION_CODE join entirely, along with the corresponding select list references (Is that where Description comes from?)

赞(0）回复(0）举报 2023-02-18

我来回答

SQL Server Cannot Query NULL or Empty Values in a Field or column in a table

1条答案

相关问题

热门标签

最新问答