Are race and ethnicity required in the CDM v6?

[Chris_Knoll]

@roger.carlson

First, use of the IS NULL clause in your WHERE clause means that an index cannot be used for the query, and a table scan will be performed. This can greatly reduce performance.

There’s an index for that:

Does SQL Server Index Null Values in a Non-Clustered Non-Unique index?

@roger.carlson

Second, in some cases, JOIN performance can be dramatically affected by NULLS. (see: https://sqlperformance.com/2015/11/sql-plan/hash-joins-on-nullable-columns ).

The specific case was the table in question has all nulls in the left table joined to a table with all nulls in the other column…not really a case we’d experence here, and it was specifically engineered to demonstrate a limitation of the optimizer. But, ok, yes, in that specific case, you get a performance problem.

@roger.carlson

Third, all things being equal, INNER JOINS are faster than OUTER JOINS. If you know all of your concept_id fields have a value, you can use inner joins. If concept_id fields can have NULLs, you must use outer joins. Considering the number of concept_id fields which must be joined back to the Concept table, that can be a huge performance issue.

If you only want records that actually have concept IDs you will need to join on the concept column and then add a WHERE CONCEPT_ID != 0 everyplace. The LEFT join in this case at least is explicit by indicating 'even if you don’t find a matching conceptID, return the row.

@roger.carlson

Given the third point above, if your concept_id field does not have nulls, it is immaterial whether you use an inner join or an outer join. The result will be the same. However, if you have nulls in a join field, inner and outer joins will return different results.

Same as above, you have to resort to ‘where concept_id != 0’ where the prior inner join on the nullable column would have removed those.

@roger.carlson

Because NULL means “Unknown”, its use adds a level of uncertainty which the explicit use of zero eliminates.

Null doesn’t mean ‘unknown’, it means ‘no value’. When you do a left join and get a null value on the right-side column, the null doesn’t mean ‘I do not know what the value of the right-side column is’ it means 'the right side has no value because the join did not match.

Because of this, it is reasonable that we have a value of ‘unknown value’ (concept_id = 0) vs. a ‘null’ value which represents ‘there is no value’. There’s a lot of discussion on this here: CONCEPT_ID (to NULL or not to NULL) [THEMIS WG3] - #7 by Chris_Knoll

@roger.carlson

Similarly, a nullable field in a WHERE clause can return different result whether or not you use ISNULL or COALESCE functions.

Not sure what you mean here? Can you provide an example?

All this aside, I feel this has taken the thread off-topic. You can follow up with this on this thread: CONCEPT_ID (to NULL or not to NULL) [THEMIS WG3] - #7 by Chris_Knoll because it is talking specifically about NULL use.