3 ms·

A better option in many cases is to check the primary key. e.g. select questions.Id , questions.Text , answers.Id , answers.Text from questions left join ans

• dspillett 4y ago
  That may be implementation or circumstance specific. For instance in MS SQL Server with a heap table (one without a clustered index) or a table where the primary key is not the clustering key, it will result in extra page reads to check the other field's value (the query planner / engine could infer from it being the PK that it can never be null, so the lookup to check is unnecessary, but IIRC it does not do this). As the columns used in the join predicate have to be read to perform the join, no extra reads will result from using them for other filtering. In your example it is very likely that the primary key is the clustering key, so will be present in the non-clustered index that I assume will be on answers.questionId, making my point moot, but if for some unusual reason neither Id nor questionId were the clustering key checking Id may result in extra reads being needed. In DBMSs without clustering keys implemented similarly to SQL Server, there may be such concerns in all cases.