Kepp all records from one table even if they are not related

Question

I have 3 tables:

T_User
UserId
Name

T_Role
RoleId
Name

T_UsersRoles
Id
FK_RoleId
FK_UserId

I want to have all records from T_Role. Nevertheless i want to pass specific UserId. Even if user doesn't have relation with some records from T_Role i want to list all T_Rule records and on the right just make value 1 if there is relation and 0 if it's not

This is my current query:

SELECT role.RoleId, role.[Name], CASE WHEN usersroles.ID IS NULL THEN 0 ELSE 1 END
FROM T_Role userrole
  LEFT JOIN T_UsersRoles usersroles ON userrole.ID = usersroles.FK_RuleID
WHERE usersroles.FK_UserID = 30;

Nevertheless i only get those records where there is relation. I thought using LEFT join i do it but it's not

so instead of getting:

1  RoleA  1
2  RoleB  0
3  RoleC  0

i got:

1  RoleA  1

because User = 30 got only one relation. How to make it as above?

score 1 · Accepted Answer · answered Oct 02 '20 at 20:17

1

A subtle trick will get it done - put the filtering on the table into the LEFT JOIN. This filters the rows on the LEFT JOINed table, but keeps all your original rows e.g.,

SELECT role.RoleId, 
    role.[Name], 
    CASE WHEN usersroles.ID IS NULL THEN 0 ELSE 1 END  
FROM T_Role role
    LEFT JOIN T_UsersRoles usersroles ON userrole.ID = usersroles.FK_RuleID AND usersroles.FK_UserID = 30;

answered Oct 02 '20 at 20:17

seanb

6,272
2
4
22

that works !! Hmm but i dont get whats the diffrence. Why not working when it is in WHERE clause? – Arie Oct 02 '20 at 20:19
If you don't have any filtering, you will get all rows from role + all the data from usersroles if it exists, the rest being NULL. Then adding the WHERE clause on usersroles removes all rows from your results that don't match - and also all the NULLs. In essence, having a WHERE clause on the LEFT JOINed table (when using specific values) turns your LEFT JOIN into an INNER JOIN. In contrast, putting the filtering in the join itself means that the rows taken from usersroles must satisfy both conditions. If it doesn't then the data from usersroles is null, and the LEFT JOIN proceeds as normal. – seanb Oct 02 '20 at 20:23
By the way - the above (with the filtering in the join) should only be used for simple cases like this. If you have complex filtering logic, a more general way of doing this is to make usersroles into a subquery e.g., `LEFT JOIN (SELECT * FROM T_UsersRoles a WHERE a.FK_UserID = 30) AS usersroles ON ...`. Of course, you will also need to remove the WHERE clause from the outer join. – seanb Oct 02 '20 at 20:33
1

The `on` clause of an `outer join` allows for `null` values, otherwise it wouldn't return rows that don't match the condition(s). A `where` clause has to be written to allow for `null` values explicitly, e.g. `... where usersroles.FK_UserID = 30 or usersroles.FK_UserID is NULL;`, otherwise references to the off-side table (right table in a `left outer join`) effectively convert an `outer join` to an `inner join`. This is a _feature_, not a _subtlety_. IMHO there is no need to introduce subqueries to obscure the purpose of the code, In complex cases I've found CTEs and comments useful. – HABO Oct 02 '20 at 20:53
... and by 'outer join' at the end of my previous post, I mean the WHERE clause in the outer query (e.g., at the end) rather than a left, right or full outer join. Just clarifying to avoid confusion. – seanb Oct 02 '20 at 20:54
1

Functionally (and at least theoretically) a [CTE and sub-query is effectively the same](https://stackoverflow.com/questions/11169550/is-there-a-performance-difference-between-cte-sub-query-temporary-table-or-ta) because SQL is declarative. Typically the execution plans will be the same. Whether you use a CTE or sub-query is just stylistic in many (especially simple) circumstances. Regarding subtlety - it's about the subtlety of where the filtering goes when writing the SQL, not a subtle feature. I think this is evidenced by the first question being 'why does this make a difference?' – seanb Oct 02 '20 at 21:02
1

A benefit of a CTE (without recursion for the present discussion) is that you can break a large query in multiple pieces and, by replacing on the final `select` statement, examine and debug the intermediate results as in [this](https://stackoverflow.com/a/61400208/92546) answer. The query optimizer sees the whole thing as a single query. – HABO Oct 03 '20 at 00:00

Kepp all records from one table even if they are not related

1 Answers1