I'm looking at an SOQL query that may either be hideously out of date or using some facet of their magical field__r.foreign_table join syntax, the code contains a reference to
USER_JOBS__R
where nothing like that currently exists as a field on the current objects there are however tables called both USER and JOBS of which JOBS contains a lookup on USER.
Has anyone ever seen this as part of the SOQL syntax?
First off, take a look at docs for Relationship Queries, it'll have everything you need.
Because the table USER_JOBS__R is plural and ends in __R it's likely going to parent to children query. USER_JOBS__R is called the "Child Relationship Name" and is created when ever a lookup field is created. If you find the lookup that defines the child to parent relationship you can confirm this by looking at the field definition (note that the __R will not be present as this is only needed with the API). In general if you have a lookup, say from an applicant to a job, whose name is JOB__C, the child relationship name will be a pluralized and __C replaced with __R to give you JOB__R. This is only the default, you can choose whatever you want for the child relationship name. Knowing this you could do the following query to give you all applicants along with each job:
[select id, (select id from APPLICANTS__R) from JOB__C]
For your scenario I would assume there is some "Job" object that has a lookup to the User object, probably USER__C, that has a child relationship name of USER_JOBS__R.