| Using greater than, less than in left join [message #581685] |
Tue, 09 April 2013 22:16  |
 |
urbanmojo
Messages: 9
Registered: March 2013
|
Junior Member |
|
|
Hello:
I am creating a query where I am trying to take phone call lengths and put them into buckets of length ranges 0:00 - 0:59, 1:00 - 1:59 etc. Even if there are no calls in the call table I need to return the range with a zero (hence the left join and nvl). When I do this the left join acts like an equal join, I suspect there is some reason left joins only work if there is an equal condition in the join (instead of >= and < that I use, or similarly I could use BETWEEN). I also have a question about performance (below).
The create table script for the lookup is like this:
CREATE TABLE DURATION_RANGES
(
RANGE_TEXT varchar2(20),
RANGE_LBOUND decimal(22),
RANGE_UBOUND decimal(22)
)
Sample inserts are:
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',0,59);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('01:00 - 01:59',60,119);
etc.
The query is:
select
r.range_text as duration_range,
nvl(count(*),0) as calls,
nvl(SUM(call_duration),0) as total_duration
from
duration_ranges r
left join
calls c
on
r.range_lbound <= c.call_duration AND r.range_ubound > c.call_duration
where
c.calldate >= '1-FEB-2013'
AND
c.calldate <= '18-FEB-2013'
group by
r.range_text
order by
r.range_text
As I say, it is not returning all ranges in the duration_ranges table, so acting like an inner join. I realize one solution would be to populate duration ranges with every value possible (instead of ranges) so join is an equal join, but that would make the duration_range table larger.
My questions:
1. Is it possible to get the left join to work with the duration range values as they currently are?
2. Even if 1 is possible, would it be better performance to have exact values (but a larger lookup table) and do an equals join instead of >=, < or BETWEEN? Performance now is not bad.
What I mean is (with only one time value and not lbound and ubound:
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',0);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',1);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',2);
etc.
Thanks!
|
|
|
|
|
|
| Re: Using greater than, less than in left join [message #581719 is a reply to message #581701] |
Wed, 10 April 2013 02:48  |
cookiemonster
Messages: 13968
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
@littlefoot - left join is an outer join, the outer keyword is optional since left and right implies it.
@urbanmojo - it's acting like an inner join because of the where clause. When an outer join fails to find a matching row in the outer joined table (calls in this case) it makes up a row of nulls. Null is not greater or less than any value.
Move what's in the where clause so that it's in the ON clause.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
