Menu
I'm a little bit out of practice with MySQL, so I hope I can find some advice for my problem here.
Basically I have two tables, call them A and B just for convenience. Both tables have a nullable column C of type varchar. When I join A and B using C, I lose all the rows where C is NULL in either table. I know this is normal in MySQL but what I would like to get is a join that includes combinations of rows where C is NULL in both tables. I found out that the query below seems to work well.
SELECT * FROM A JOIN B ON (A.C IS NULL AND B.C IS NULL) OR (A.C IS NOT NULL AND B.C IS NOT NULL AND A.C = B.C) So my question is, is this query the best I can get or is there a way to make this join better? Thanks!
642
Since it's not possible to join on NULL values in SQL Server like you might expect, we need to be creative to achieve the results we want. One option is to make our AccountType column NOT NULL and set some other default value. Another option is to create a new column that will act as a surrogate key to join on instead.
Because null values represent unknown or inapplicable values, Transact-SQL has no basis to match one unknown value to another. You can detect the presence of null values in a column from one of the tables being joined only by using an outer join.
As we have seen from the above examples joining NULL values does not work. Even though you have two NULL values SQL Server does not treat these as the same value. Internally a value of NULL is an unknown value and therefore SQL Server does not equate an unknown value being equal to another unknown value.
A join that displays only the rows that have a match in both joined tables. Columns containing NULL do not match any values when you are creating an inner join and are therefore excluded from the result set. Null values do not match other null values.
Use the NULL-safe equal operator:
SELECT * FROM A JOIN B ON A.C <=> B.C If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
