Mastering RLQUERY & addJoinQuery(): Optimize Your Queries Like a Pro

[Kristy Merriam]

ServiceNow developers often need to query related records efficiently. Instead of writing multiple database queries or using slow nested loops, there’s a better way—RLQUERY and addJoinQuery().

What is RLQUERY?

RLQUERY (Related List Query) lets you filter records based on related lists without extra queries. This means you can get exactly the data you need in a single, optimized step.

Example: Filtering Groups with Active Users

Imagine you need to find all groups where at least one member is active. Normally, you’d:

1. Query the user group table
2. Store group IDs in an array
3. Query active users separately

With RLQUERY, you can do this in one step:

var grGroup = new GlideRecord('sys_user_group');
grGroup.addEncodedQuery('^RLQUERYsys_user_grmember.group,>=1,m2m^user.active=true^ENDRLQUERY');
grGroup.query();
gs.info(grGroup.getRowCount());
while (grGroup.next()) {
gs.info('Group (RLQUERY): ' + grGroup.name);
}

This approach is faster and cleaner than writing multiple queries.

Finding RLQUERY Syntax Using ServiceNow Reports

If you’re unsure about RLQUERY syntax, here’s a trick:

1. Create a new report in ServiceNow
2. Select your table (e.g., sys_user_group)
3. Add a related list condition (e.g., "Group members where active = true")
4. Save the report and open the XML view
5. Copy the encoded query string for use in scripts

What About addJoinQuery()?

While RLQUERY is great for filtering related records, addJoinQuery() lets you join two tables inside a GlideRecord query.

Example: Find all active users assigned to at least one incident

var grUser = new GlideRecord('sys_user');
grUser.addActiveQuery(); // Only active users

// Join the incident table where assigned_to matches sys_id of user
var joinQuery = grUser.addJoinQuery('incident', 'sys_id', 'assigned_to');
joinQuery.addCondition('state', '!=', 7); // Exclude closed incidents

grUser.query();
while (grUser.next()) {
gs.info('User with open incidents: ' + grUser.name);
}

This returns only users with at least one open incident—something impossible with standard walking queries.

Best Practices for Query Optimization

🚀 Know your relationships – Double-check table references to avoid mistakes
⚡ Monitor performance – Overcomplicated queries can slow down the database
🛠 Test in sub-prod – One typo in an encoded query can break everything
📖 Document your queries – Future developers (and future you) will thank you

Conclusion

Whether you’re using RLQUERY for related list filtering or addJoinQuery() for cross-table joins, these techniques will make your ServiceNow scripts more efficient and maintainable.

Have you used these methods before? Share your experiences in the comments! And if you found this helpful, be sure to subscribe for more ServiceNow development tips.