Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I'm testing some queries with an old version of Informix (9.2.1) and can't seem to execute even the most basic queries containing RIGHT OUTER JOIN or FULL OUTER JOIN. Tried several variations (RIGHT JOIN, OUTER JOIN or just OUTER), but without any success. INNER JOIN and LEFT JOIN work fine.

Does anyone know the correct syntax for outer/right joins in older versions of Informix, or are these joins just not possible?

share|improve this question

1 Answer

up vote 2 down vote accepted

Ok, at least an outer join works just with different syntax: just using OUTER with a comma after the first table and a WHERE instead of an ON.

Edited to add an example from http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls679.htm

SELECT c.customer_num, c.lname, o.order_num FROM customer c, 
   OUTER orders o WHERE c.customer_num = o.customer_num
share|improve this answer
Sorry to ask and answer the question so quickly, wasn't expecting my colleague to know the answer. – newenglander May 9 '12 at 14:26
Can you add an example of this syntax? It sounds weird, at least. – ypercube May 9 '12 at 14:58
And by the way, there is no DBMS that has OUTER JOIN or just OUTER. There are three outer joins, the LEFT OUTER JOIN or just LEFT JOIN, the RIGHT OUTER JOIN or just RIGHT JOIN and the FULL OUTER JOIN or just FULL JOIN. Some DBMS have all 3, some have only the Left and Right. Perhaps Informix has only the LEFT OUTER JOIN. – ypercube May 9 '12 at 15:00
2  
and FROM a , b WHERE a.id = b.id is a hidden inner join, not outer. – ypercube May 9 '12 at 15:13
@ypercube: Newer versions of Informix have LEFT, RIGHT, and FULL outer joins. I mentioned OUTER JOIN and OUTER referring to different possibilities for the syntax, not different joins. @RolandoMySQLDBA: Thanks for jumping in with an example and link. – newenglander May 9 '12 at 16:56
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.