Search code examples
sqljoinnullable

How to SQL join when join-id is nullable

Struggling with a SQL query that won't join

SELECT * 
FROM AspNetUsers u
JOIN Groups g ON g.Id = u.GroupId

In the AspNetUsers table, I have these columns:

Id INT
GroupId INT (nullable)

In the Groups table, I have:

Id INT
GroupName VARCHAR(255)

(There is a FK set up between the userId and GroupId so no type mismatch)

I get this error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Groups g ON g.Id = u.GroupId

Can I not join on a nullable column? I just want to retrieve those rows where there is a matching GroupId. How would you go about this

Solution

  • I might be wrong, but: groups is MySQL reserved word (see here) which means that it can't be used for table or column name; I'd say that this is the reason for error you got. Maybe you should enclose table name into double quotes (and match letter case)?

    As of your question: certainly, you can perform join on NULL values; you just won't get any result. In that case, see whether outer join helps in what you're doing.