Muliple NULL values found in Jira database query results. What do they mean?

[Deleted user]

I ran the following query:

select u.id as "User Id", a.user_key as "App User Key", u.lower_user_name as "Lower Username", u.active as "User status", ud.id as "User Directory Id", ud.directory_position as "User Directory Order", ud.directory_name as "User Directory Name", ud.active as "User Directory Status", g.id as "Group Id", g.group_name as "Group Name", gd.id as "Group Directory Name", gd.directory_name as "Group Directory Name", gd.active as "Group Directory Status"
from cwd_user u
left join app_user a on a.lower_user_name = u.lower_user_name
join cwd_directory ud on ud.id = u.directory_id
left join cwd_membership m on m.child_id = u.id
left join cwd_group g on g.id = m.parent_id
left join cwd_directory gd on gd.id = g.directory_id
order by ud.directory_position ASC;

As a result I find many records with NULL value in field such as: Group Id, Group Name, Group Directory Name, Group Directory Status. The App User Key and Lower Usernames are always filled as expected.

Question: What does these records have NULL value fields?

[Fabio Racobaldo _Catworkx_ · Accepted solution]

Hi @[deleted] ,

I think that null values are due to use of LEFT JOIN. 

INNER JOIN gets all records that are common between both tables based on the supplied ON clause.

LEFT JOIN gets all records from the LEFT linked and the related record from the right table ,but if you have selected some columns from the RIGHT table, if there is no related records, these columns will contain NULL.

Please try using the following query :

select u.id as "User Id", a.user_key as "App User Key", u.lower_user_name as "Lower Username", u.active as "User status", ud.id as "User Directory Id", ud.directory_position as "User Directory Order", ud.directory_name as "User Directory Name", ud.active as "User Directory Status", g.id as "Group Id", g.group_name as "Group Name", gd.id as "Group Directory Name", gd.directory_name as "Group Directory Name", gd.active as "Group Directory Status"
from cwd_user u
inner join app_user a on a.lower_user_name = u.lower_user_name
inner join cwd_directory ud on ud.id = u.directory_id
inner join cwd_membership m on m.child_id = u.id
inner join cwd_group g on g.id = m.parent_id
inner join cwd_directory gd on gd.id = g.directory_id
order by ud.directory_position ASC;

This should fix your issue.

Fabio

[Deleted user]

@Fabio Racobaldo _Catworkx_ Yes!! This solved my question. Thank you!!

[Fabio Racobaldo _Catworkx_]

I'm happy for that @[deleted] .

Please mark my answer as accepted ;)