CODEWITHSUNDEEP
sqloracle-databasejoinexists
Peshal
Peshal

Reputation: 1518

Multiple Table Join, inner join vs Exists

I am having issue getting the right query for a task. Lets say we have 4 tables with following columns

Table  Column   Column

one    abc

two    abc      cde

three  cde      def

four   def      dt

Now the column dt in table four is a Date datatype.

I am trying to get all the data from table one where date in the dt is greater than say 2012/01/01. columns abc, cde and def are related

I am doing something like this:

Select * 
from one t 
where Exists (Select a.abc 
              from two a 
              where a.abc = t.abc 
                and Exists (Select b.cde, b.def 
                            from three b 
                            where b.cde = a.cde 
                              and Exists (select c.def
                                          from four c 
                                          where c.def= b.def 
                                            abd c.dt >= toDate('2012-01-01', 'YYYY-MM-DD')
                                         )
                           )
              );

I tried the same thing using inner join, But the inner join is giving row count that is actually more that the table has in some cases. Basically its duplicating the rows. Any help is much appreciated. I looked at distinct as well, looks like it increases the cost.

Upvotes: 0

Views: 2782

Answers (2)

andypp
andypp

Reputation: 249

Complementing Felipe Silva's answer, you can put the where .. in statement on the outer layer to remove the duplicates

SELECT t1.*
FROM one t1 where t1.abc in (
  select t2.abc from two t2 
  INNER JOIN three t3 ON t3.cde = t2.cde
  INNER JOIN four t4 ON t4.def = t3.def
  WHERE t4.dt >= toDate('2012-01-01', 'YYYY-MM-DD')
);

Upvotes: 1

Filipe Silva
Filipe Silva

Reputation: 21657

That could be done like this. Can't see how you did it to give you more rows than the table count:

SELECT t1.*
FROM one t1
INNER JOIN two t2 ON t2.abc = t1.abc
INNER JOIN three t3 ON t3.cde = t2.cde
INNER JOIN four t4 ON t4.def = t3.def
WHERE t4.dt >= toDate('2012-01-01', 'YYYY-MM-DD');

Upvotes: 1

Related Questions