Subquery found in JOIN SQL-L042

JOIN clauses should not contain subqueries. Use Common Table Expression(CTE, aka WITH statement)s instead.

Exception

Some dialects don't allow CTEs, for those dialects this rule should be disabled.

Bad practice

SELECT
    a.x, a.y, b.z
FROM a
JOIN (
    SELECT x, z from b
) USING(x)

Recommended

WITH c as (
    SELECT x, z from b
)
SELECT
    a.x, a.y, c.z
FROM a
JOIN c USING(x)

References

• CTEs vs Subqueries