Home » SQL & PL/SQL » SQL & PL/SQL » Using greater than, less than in left join (Oracle 11)
Using greater than, less than in left join [message #581685] Tue, 09 April 2013 22:16 Go to next message
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 #581701 is a reply to message #581685] Wed, 10 April 2013 00:59 Go to previous messageGo to next message
Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
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)

I suppose that you should rather use outer join in such cases.
Re: Using greater than, less than in left join [message #581719 is a reply to message #581701] Wed, 10 April 2013 02:48 Go to previous messageGo to next message
cookiemonster
Messages: 13967
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.
Re: Using greater than, less than in left join [message #581721 is a reply to message #581719] Wed, 10 April 2013 02:56 Go to previous messageGo to next message
Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you! It is more than obvious that I'm not into ANSI join syntax at all (shame on me /forum/fa/1606/0/, my apologies).
Re: Using greater than, less than in left join [message #581780 is a reply to message #581721] Wed, 10 April 2013 10:24 Go to previous message
urbanmojo
Messages: 9
Registered: March 2013
Junior Member
@littlefoot - you gave me nightmares about all the existing code I have out there...Smile
@cookiemonster - thanks, that works. I'll add that count(*) is no good here, have to do something like count(call_duration) otherwise nulls will be counted.

C is for cookie--that's good enough for me. Smile
Previous Topic: SQL
Next Topic: error while using cursor
Goto Forum:
  


Current Time: Sun Aug 24 17:17:20 CDT 2025