SQL: How to use WHERE instead of OUTER JOIN

Question

I am using an old database call Centura Gupta that doesn't have the join clauses (left join, right join, inner join, outer join). So I need to use where to replace the outer join that I need:

SELECT *
FROM table1
OUTER JOIN table2
ON table1.id_person table2.id_person;

I think that where can only replaces inner join:

SELECT *
FROM table1
WHERE table1.id_person = table2.id_person;

(1) There is no such thing as `OUTER JOIN`. (2) Tag with the database you are really using. I know of no database that doesn't support `JOIN` and hasn't for the past 15 years (so I'm curious). (3) Provide sample data and desired results. — Gordon Linoff, Jan 28 '21 at 14:39
Do you need `FULL JOIN` ? or only left/right join? BTW: table2 is missing in your second query. — wildplasser, Jan 28 '21 at 14:42
Based on my searches, I'm wondering if this is OpenText SQLBase. This syntax guide looks helpful. There is a section on joins, including "outer joins": http://apachepersonal.miun.se/~parfor/databaser00/lang.pdf — Steve Lovell, Jan 28 '21 at 14:59
Comma is cross join (with lower precedence than keyword joins) & inner join on is cross join where. Expressing left & other outer joins by join is a faq. Expressing join on with cross join & where is a faq. — philipxy, Jan 28 '21 at 18:52

score 0 · Answer 1 · answered Jan 28 '21 at 14:46

0

You could try to use subqueries instead of join

SELECT *
FROM table1 t1
WHERE id_Person IN 
(
SELECT id_Person
FROM table2 
)

answered Jan 28 '21 at 14:46

Jiacheng Gao

365
3
9

This is not the equivalent of a JOIN. – wildplasser Jan 28 '21 at 14:55
ok, please provide column names that you needed, with the table name. I'll update subquery – Jiacheng Gao Jan 28 '21 at 14:58

score 0 · Answer 2 · answered Jan 28 '21 at 15:52

0

I used this kind of implementation when I didn't know JOINs. May not be exactly right but something which can get you close:

SELECT *
FROM table1 t1, table2 t2
WHERE t1.id_Person=t2.id_Person;

answered Jan 28 '21 at 15:52

Suffer Surfer

15
6

score 0 · Answer 3 · answered Jan 28 '21 at 19:52

I don't know about that particular database, but you might be able to use a correlated subquery to get "joined" data. This will pull all records from table1 and the related info from table2, or NULL for whatever4 and whatever5 if there's no matching id_person in table2:

SELECT t1.whatever1
    , t1.whatever2
    , t1.whatever3
    , (SELECT whatever4 FROM table2 AS t2 WHERE t2.id_person = t1.id_person) AS whatever4
    , (SELECT whatever5 FROM table2 AS t2 WHERE t2.id_person = t1.id_person) AS whatever5
FROM table1 AS t1

score 0 · Answer 4 · answered Jan 29 '21 at 01:57

'Old database called Centura Gupta' ??
Maybe you actually mean OpenText Gupta SQLBase - which is by no means 'old'.
You may be running an 'old' version of SQLBase - but it is now up to v12.2 native 64bit, and outer joins are certainly supported. You can either use the native Gupta outer joins - similar to Oracle (+) or standard ANSI outer joins.
If you want to use ANSI OUTER joins, specify 'ANSIJoinSyntax=1' in your Server side Sql.ini

Go here for more SQLBase Join syntax: Gupta SQLBase Manuals

Native Gupta Outer Join:

SELECT t1.id_person, t2.id_person
From table1 t1 , table t2
Where t1.id_person = t2.id_person(+)

ANSI Outer Join:

SELECT t1.person_id, t2.person_id   
From table1 t1
Left Outer Join table2 t2 ON t1.id_person = t2.id_person
Where <what ever filter you want>

Go here for more SQLBase Join syntax: Gupta SQLBase Manuals

SQL: How to use WHERE instead of OUTER JOIN

4 Answers4