Support for joins is required only on the jcr:path column and only for joining a primary node type table with a mixin node type tables. For example:
SELECT nt:file.jcr:path, jcr:lockOwner
FROM nt:file, mix:lockable
WHERE jcr:lockOwner = 'John'
AND mynt:file.jcr:path=mix:lockable.jcr:path
Additional join support, on other columns and with other tables, is optional.