Thread: Oracle outer join porting question
i'm trying to port an existing application from Oracle8i to PostgreSQL but i'm having problems understanding a certain outer join query type used in the application. the query includes a normal outer join between two tables but also uses outer join syntax to join a table with a constant. here's a simplified version of the query: SELECT doc.id,doc.title,sub.user_id,sub.operation FROM document doc, document_subscription sub WHERE 6 = sub.user_id(+) ANDsub.document_id(+) = doc.id; what does the '6 = sub.user_id(+)' condition exactly do in this query? how would this be translated SQL92 join syntax used by PostgreSQL? i've tried converting it to: SELECT doc.id,doc.title,sub.user_id,sub.operation FROM document doc LEFT OUTER JOIN document_subscription sub ON sub.document_id= doc.id WHERE (sub.user_id = 6 OR sub.user_id IS NULL); but this query is missing the rows in the documents table which have a corresponding document_subscription row with 'not user_id = 6'. here're also simplified definitions of the two tables used in the query and some test data: CREATE TABLE document ( id INTEGER, title VARCHAR(100), PRIMARY KEY(id) ); CREATE TABLE document_subscription ( document_id INTEGER NOT NULL, user_id INTEGER NOT NULL, operation VARCHAR(10) ); INSERT INTO document VALUES (1, 'doc1'); INSERT INTO document VALUES (2, 'doc2'); INSERT INTO document VALUES (4, 'doc4'); INSERT INTO document_subscription VALUES (1, 5, 'op1'); INSERT INTO document_subscription VALUES (2, 5, 'op2'); INSERT INTO document_subscription VALUES (2, 6, 'op2'); best regards, -- aspa http://www.kronodoc.fi/
On 15 Jan 2003 at 16:31, Marko Asplund wrote: > > i'm trying to port an existing application from Oracle8i to PostgreSQL but > i'm having problems understanding a certain outer join query type used in > the application. the query includes a normal outer join between two tables > but also uses outer join syntax to join a table with a constant. here's a > simplified version of the query: > > SELECT doc.id,doc.title,sub.user_id,sub.operation > FROM document doc, document_subscription sub > WHERE 6 = sub.user_id(+) AND sub.document_id(+) = doc.id; > > what does the '6 = sub.user_id(+)' condition exactly do in this query? > how would this be translated SQL92 join syntax used by PostgreSQL? > > i've tried converting it to: > > SELECT doc.id,doc.title,sub.user_id,sub.operation > FROM document doc LEFT OUTER JOIN document_subscription sub > ON sub.document_id = doc.id > WHERE (sub.user_id = 6 OR sub.user_id IS NULL); > > but this query is missing the rows in the documents table which have a > corresponding document_subscription row with 'not user_id = 6'. What about this: SELECT doc.id,doc.title,sub.user_id,sub.operation FROM document doc LEFT OUTER JOIN document_subscription sub ON sub.document_id= doc.id; id | title | user_id | operation ----+-------+---------+----------- 1 | doc1 | 5 | op1 2 | doc2 | 5 | op2 2 | doc2 | 6 | op2 4 | doc4 | | (4 rows) > > here're also simplified definitions of the two tables used in the query > and some test data: Thanks for supplying the table and data. That makes things much easier. -- Dan Langille : http://www.langille.org/
> -----Original Message----- > From: Marko Asplund [mailto:aspa@kronodoc.fi] > Sent: Wednesday, January 15, 2003 4:31 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] Oracle outer join porting question > > > > i'm trying to port an existing application from Oracle8i to > PostgreSQL but > i'm having problems understanding a certain outer join query > type used in > the application. the query includes a normal outer join > between two tables > but also uses outer join syntax to join a table with a > constant. here's a > simplified version of the query: > > SELECT doc.id,doc.title,sub.user_id,sub.operation > FROM document doc, document_subscription sub > WHERE 6 = sub.user_id(+) AND sub.document_id(+) = doc.id; > > what does the '6 = sub.user_id(+)' condition exactly do in > this query? > how would this be translated SQL92 join syntax used by PostgreSQL? > > i've tried converting it to: > > SELECT doc.id,doc.title,sub.user_id,sub.operation > FROM document doc LEFT OUTER JOIN document_subscription sub > ON sub.document_id = doc.id > WHERE (sub.user_id = 6 OR sub.user_id IS NULL); > > but this query is missing the rows in the documents table which have a > corresponding document_subscription row with 'not user_id = 6'. > Try this: SELECT doc.id,doc.title,sub.user_id,sub.operation FROM document doc LEFT OUTER JOIN document_subscription sub ON sub.document_id= doc.id AND sub.user_id = 6; Tambet
On 15 Jan 2003 at 16:40, Tambet Matiisen wrote: > Try this: > > SELECT doc.id,doc.title,sub.user_id,sub.operation > FROM document doc LEFT OUTER JOIN document_subscription sub > ON sub.document_id = doc.id AND sub.user_id = 6; FWIW: test=# SELECT doc.id,doc.title,sub.user_id,sub.operation test-# FROM document doc LEFT OUTER JOIN document_subscription sub test-# ON sub.document_id = doc.id AND sub.user_id = 6;id | title | user_id | operation ----+-------+---------+----------- 1 | doc1 | | 2 | doc2 | 6 | op2 4 | doc4 | | -- Dan Langille : http://www.langille.org/
On Wed, 15 Jan 2003, Tambet Matiisen wrote: > ... > Try this: > > SELECT doc.id,doc.title,sub.user_id,sub.operation > FROM document doc LEFT OUTER JOIN document_subscription sub > ON sub.document_id = doc.id AND sub.user_id = 6; yes, this query seems to give the same results as the original one. thanks! best regards, -- aspa http://www.kronodoc.fi/