Home » SQL & PL/SQL » SQL & PL/SQL » Normal join and outer join (Oracle 11G)
Normal join and outer join [message #598920] Sat, 19 October 2013 17:38 Go to next message
sherlocksher
Messages: 14
Registered: June 2013
Junior Member
Hi,

Lets say I have three tables t1 and t2 and t3.

SELECT * FROM T1;

Id
____
1
2
3
4

SELECT * FROM T2;

Id
____
1



SELECT * FROM T3;

Id
____
1

Now when data exists in T2 and T3, I want to return only the records in T1 that match the records in T2 and T3 which is basically a normal join

select t1.id from t1, t2,t3 where t1.id = t2.id and t1.id = t3.id

However when there are no records in T2 or T3, I want to return all records in T1 i.e 1,2,3,4

One way of doing that is using the not exists clause

select * from t1 where not exists ( select null from t2 where t2.Id != t1.id) and not exists ( select null from t3 where t1.Id != t3.id)

Is there a better way of doing this in sql ?

[Updated on: Sat, 19 October 2013 17:54]

Report message to a moderator

Re: Normal join and outer join [message #598922 is a reply to message #598920] Sat, 19 October 2013 18:45 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Isn't a left outer join that you try to do?
SQL> CREATE TABLE t1(n NUMBER);

Table created.

SQL> INSERT INTO t1 (SELECT level lvl FROM "PUBLIC".dual CONNECT BY level <= 5);

5 rows created.

SQL> CREATE TABLE t2(n NUMBER);

Table created.

SQL> SELECT a1.n FROM t1 a1 LEFT OUTER JOIN t2 a2 ON (a1.n = a2.n);

         N
----------
         5
         4
         3
         1
         2
Re: Normal join and outer join [message #598923 is a reply to message #598922] Sat, 19 October 2013 19:02 Go to previous messageGo to next message
sherlocksher
Messages: 14
Registered: June 2013
Junior Member
No.

A left outer join will bring all the records in T1. I want the records in T1 that match records in T2 ( if they exist) or all records of T1.

When there are no records in T2, I agree that Left outer join is the way to go. However if there are matching records in T2, then I want a normal join.
Re: Normal join and outer join [message #598924 is a reply to message #598923] Sat, 19 October 2013 19:43 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
sherlocksher wrote on Sun, 20 October 2013 02:02
No.

A left outer join will bring all the records in T1. I want the records in T1 that match records in T2 ( if they exist) or all records of T1.

When there are no records in T2, I agree that Left outer join is the way to go. However if there are matching records in T2, then I want a normal join.

But, you are aware, that if T2 or T3 contain more than one row with ID filled (NOT NULL), your query will not return anything?
-- Sample data
with t1 as ( select column_value id from table ( sys.odcinumberlist(1,2,3,4) ) )
   , t2 as ( select column_value id from table ( sys.odcinumberlist(1,2) ) )
   , t3 as ( select column_value id from table ( sys.odcinumberlist(1,2) ) )
-- The original query
select * from t1
where not exists ( select null from t2 where t2.Id != t1.id)
  and not exists ( select null from t3 where t1.Id != t3.id)
/

What about transforming your query specification into SQL directly (supposing that "outer join" is done when both T2 and T3 are empty)?
select * from t1
where ( exists ( select null from t2 where t2.Id = t1.id) or ( select count(*) from t2 ) = 0 )
  and ( exists ( select null from t3 where t3.Id = t1.id) or ( select count(*) from t3 ) = 0 )
  --and ( select count(*) from t2 where id is not null ) <= 1
  --and ( select count(*) from t3 where id is not null ) <= 1
/

Note the last two commented conditions - they are there to match the query you posted for multiple rows in T2 or T3.
icon3.gif  Re: Normal join and outer join [message #598928 is a reply to message #598920] Sun, 20 October 2013 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Now when data exists in T2 and T3, I want to return only the records in T1 that match the records in T2 and T3 which is basically a normal join
...
However when there are no records in T2 or T3, I want to return all records in T1 i.e 1,2,3,4

(Assuming "id" is a primary key.)

select id from t1 natural join t2
union 
select id from t1 natural join t3
union 
select id from t1 where not exists (select null from t2 where rownum=1)
union 
select id from t1 where not exists (select null from t3 where rownum=1)
/

[Updated on: Sun, 20 October 2013 01:12]

Report message to a moderator

Re: Normal join and outer join [message #599044 is a reply to message #598928] Mon, 21 October 2013 15:09 Go to previous message
sherlocksher
Messages: 14
Registered: June 2013
Junior Member
Thank you Flyboy and Michael Cadot for your replies.

Previous Topic: Materialized view
Next Topic: PL/SQL Error
Goto Forum:
  


Current Time: Thu Mar 28 03:27:30 CDT 2024