Sub-select not allowed in JOIN clause

zeolite · #1 (**permalink**) 06-12-09, 14:49

When I execute some SQL similar to the following I get an error message saying that sub-selects are not supported on joins. I cannot work out whether there is a logical reason for this or it is just a limitation of my DBMS.

Code:

SELECT
    ....
FROM
    t1
LEFT JOIN
    t2
ON
    t1.id = t2.id
AND t2.foo = (SELECT
                  MIN(t3.foo)
              FROM
                  t3
              WHERE
                  t2.x = t3.y)

I have only tried running this code on Ingres 9.2. I suspect that the Query Optimizer might just not be advanced to generate a Query Execution Plan for this level of nesting. However, there may be some logical reason why this is impossible that I am overlooking. Any thoughts?

sco08y · #2 (**permalink**) 06-17-09, 02:01

Quote:

Originally Posted by zeolite

When I execute some SQL similar to the following I get an error message saying that sub-selects are not supported on joins. I cannot work out whether there is a logical reason for this or it is just a limitation of my DBMS.Any thoughts?

The columns you specify for an inner join are supposed to be columns the DBMS can use to match rows between tables. What you're trying to do is filter rows, so it should be in a WHERE clause. (Or a HAVING clause...)

Keep in mind that any SELECT foo FROM a JOIN b ON a.x = b.x is simply syntactic sugar for SELECT foo FROM a CROSS JOIN b WHERE a.x = b.x. Recall that CROSS JOIN means "find all possible combinations of rows", but once you restrict the results to only those rows with a matching column, you get a regular inner join. That's why it works the way it does.

zeolite · #3 (**permalink**) 06-17-09, 04:07

Thanks for the info. That helps a lot

Internet Services

Web Development

Digital Marketing

Consumer Tech