Add NULL to JOIN suggestions
When joining two tables, you today get the option to join on known references or identical column names.
What I would like to see is an extra join option in the suggestions, if both the columns are NULLable:
SELECT * FROM table1
INNER JOIN table2 ON (table2.ref1ID = table1.ref1ID OR (table2.ref1ID IS NULL AND table1.ref1ID IS NULL)
The above example might seem like rubbish at first! But sometimes when you have composite keys, one of the columns in the key might very well be a NULLable value.
So a more valid example would be:
SELECT * FROM table1
INNER JOIN table2 ON table2.comp1 = table1.comp1 AND (table2.ref1ID = table1.ref1ID OR (table2.ref1ID IS NULL AND table1.ref1ID IS NULL)
Ole Frederiksen
shared this idea
-
Jim Nguyen
commented
I would be careful with the resulting query in this recommendation. When you add the functions to the joining columns, it eliminates the SARGable characteristic and may impact the resulting query performance.