CODEWITHSUNDEEP
sqloracle-databasejoindistinct
panuffel
panuffel

Reputation: 684

DISTINCT in JOIN

I have a question in Oracle SQL.

To simplify my problem, let's say I have two tables:

TAB1:                TAB2:
Usr  Fruit            Fruit  Calories
1    A                A      100
1    A                B      200
1    A                C      150
1    C                D      400
1    C                E      50
2    A
2    A
2    E

It's important that there are double entries in TAB1. Now I want to know the calories for usr 1. But by joining both tables

SELECT TAB2.calories from TAB1
JOIN TAB2 ON TAB1.Fruit = TAB2.Fruit
WHERE TAB1.Usr = 1;

I get double results for the double entries. I could of course use distinct in the header, but is there a possibility to distinct the values (to A and C) directly in the join? I am sure that would improve my (much larger) performance.

Thanks!

Upvotes: 0

Views: 251

Answers (4)

A ツ
A ツ

Reputation: 1267

since you select nothing in tabA and maybe you have some usefull index, i'd go for an IN instead of the join

SELECT TAB2.calories 
FROM TAB2
WHERE TAB2.Fruit IN ( SELECT TAB1.Fruit FROM TAB1 WHERE TAB1.Usr = 1)

i'm pretty sure this one will take longer but you can still try: 

SELECT TAB2.calories 
FROM TAB2
WHERE TAB2.Fruit IN ( SELECT DISTINCT TAB1.Fruit FROM TAB1 WHERE TAB1.Usr = 1)

Upvotes: 1

Hambone
Hambone

Reputation: 16377

I'm a big fan of the semi-join. For tables this small, it won't matter, but for larger tables it can make a big difference:

select
  tab2.calories
from tab2
where exists (
  select null
  from tab1
  where tab1.fruit = tab2.fruit and tab1.usr = 1
)

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You should do distinct before the join

select sum(tab2.calories) as TotalCalories
from (select distinct tab1.*
      from tabl
     ) t1 join
     tab2
     on t1.fruit = tab2.fruit
where t1.user = 1;

Also, to add the values, use an aggregation function.

Upvotes: 1

Jorge Campos
Jorge Campos

Reputation: 23361

Try as this:

SELECT TAB2.calories 
 from (select distinct usr, fruit from TAB1) as T1
        JOIN TAB2 ON T1.Fruit = TAB2.Fruit
WHERE T1.Usr = 1;

Upvotes: 1

Related Questions