Re: Trouble w/ a JOIN / UNION query

[Bill Karwin]

Okay, I tried a similar query on my 'mysql' database:

SELECT user FROM user
UNION
(SELECT user FROM db)

And I got the same error in phpMyAdmin. I also get a similar error even using the mysql CLI, even though it doesn't append a LIMIT clause.

The error goes away if you put parens around the first query in the union, like the following:

(SELECT user FROM user)
UNION
(SELECT user FROM db)

Other side comments, not related to the error above:

You have a mismatch in the number of columns between these two unioned queries. That should be an error too.

I also notice that in the second query, you're testing cart_ordr_hdr.ordr_id in the WHERE clause, which invalidates the outer join. You might as well do an inner join. The reason is that if you use columns from the right side of a left outer join in an equality comparison in the WHERE clause, the condition will return false everywhere you have NULL in those columns. So the "outer" rows (rows that would be returned by an outer join, but not by an inner join) will always be eliminated.

Regards,
Bill K.