Solved

Trying to understand a WHERE clause statement

Posted on 2014-02-24
16
279 Views
Last Modified: 2014-04-09
Let's say I have  a table with columns Col1, Col2, ColBeginDate and ColEndDate. vDate is a parameter to the procedure.

If I have this in the WHERE clause:

WHERE vDate between ColBeginDate(+) AND ColEndDate(+)

1)
What does this mean? I would think it would give all rows where vDate is between those two dates and all rows where vDate is not between those dates. If that is the case, why do we need to include the WHERE clause?

2)
Does it mean that whatever rows are returned by the SELECT and after all other WHERE clause conditions are satisfied, a filter will be placed on the result as follows:
vDate between ColeBeginDate and ColEndDate. So only take those rows.

Appreciate an answer.
0
Comment
Question by:soccerplayer
  • 6
  • 3
  • 3
  • +3
16 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39883849
Are you asking about the BETWEEN or the '(+)' on the columns?

If it is the '(+)', this is the old Oracle Outer Join syntax.  

I've never seen it used the way you posted but it looks like it is valid syntax:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4963137609733

You shouldn't need if with a variable and table columns.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39883878
Is there more about those tables in the WHERE clause?
In general you should switch to the ANSI outer join syntax, to keep out of misinterpretation ;).
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39883904
The (+) would be required if the table that the dates are in is outer joined to, even with the variable.

Without the full where clause, this part would mean that either the variable is between the 2 dates or there is no record in the table as a result of the join.
0
 

Author Comment

by:soccerplayer
ID: 39883917
I had asked a similar question before:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28246372.html

I am trying to understand that solution in the context of this table structure. I thought I understood it at that time but this doesn't make sense to me know.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39883924
>>I had asked a similar question before:

Same thing.  It's an outer join.

>>I thought I understood it at that time but this doesn't make sense to me know.

As suggested above:  Port it into the ANIS syntax.  That syntax is much easier to read.

As far as the old syntax you have, I have found it best to create simple test tables and play with the outer join to 'see' it in action.
0
 

Author Comment

by:soccerplayer
ID: 39883934
johnsone, if that is the case, why don't we write it as:

vDate BETWEEN ColBeginDate AND ColEndDate

which is a straight filter. I don't think that's what it is.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39883938
>>vDate BETWEEN ColBeginDate AND ColEndDate

I'm not johnsone but if you aren't joining tables, there is no need to do an outer join.
0
 

Author Comment

by:soccerplayer
ID: 39883961
slightvw, I am joining tables. There are multiple tables in this SQL. I just posted the question with one. This particular clause does not make sense. stdstuber explained it in the other question but I really don't understand it in this context.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 34

Expert Comment

by:johnsone
ID: 39883963
Without the context of the full where clause, removing the (+) would give you different results if whatever table those columns are in is outer the object of an outer join.  If it is a straight join, then the (+) is not necessary.
0
 

Author Comment

by:soccerplayer
ID: 39883991
johnsone, I don't understand this part of your statement:


"are in is outer the object of an outer join"


Can you please elaborate?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39884008
I guess I need to proofread better.


Without the context of the full where clause, removing the (+) would give you different results if whatever table those columns are in is the object of an outer join.  If it is a straight join, then the (+) is not necessary.

In other words, these 2 queries could give different results (assuming the date fields are in tab2)

select ...
from tab1, tab2
where tab1.col1 = tab2.col1(+) and
v_date between tab2.beg_date(+) and tab2.end_date(+);

select ...
from tab1, tab2
where tab1.col1 = tab2.col1(+) and
v_date between tab2.beg_date and tab2.end_date;

They would only give different results if there are rows in tab1 with a value in col1 that does not have a corresponding record in tab2.  You are essentially negating the outer join.  It was a screwy syntax, but we all had to know it.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 39884910
it's always best to evaluate based on results
create table mt (id integer, data varchar2(200), dt date);

create table dt (id integer, data varchar2(200), dt_start date, dt_end date);


insert into mt select 1, 'a', trunc(sysdate-10) from dual;
insert into mt select 2, 'b', trunc(sysdate-5) from dual;
insert into mt select 3, 'c', trunc(sysdate-1) from dual;

insert into dt select 1, 'a1', trunc(sysdate-11), trunc(sysdate-9) from dual;
insert into dt select 2, 'a2', trunc(sysdate-10), trunc(sysdate-8) from dual;
insert into dt select 3, 'b1', trunc(sysdate-6), trunc(sysdate-4) from dual;
insert into dt select 4, 'd1', trunc(sysdate-3), trunc(sysdate-2) from dual;

Open in new window


this query doesn't use (+)'es, thus being an inner join
select mt.id, mt.data, mt.dt, dt.id detail_id, dt.data detail_data, dt.dt_start, dt.dt_end
from mt, dt
where mt.dt between dt.dt_start and dt.dt_end; 

Open in new window

inner join

this query uses (+)es, thus being a left outer join.
> all the rows in the left table will be displayed
matched to any optional rows in the right table
if the tables are put in the correct order, the left and right actually make sense
select mt.id, mt.data, mt.dt, dt.id detail_id, dt.data detail_data, dt.dt_start, dt.dt_end
from mt, dt
where mt.dt between dt.dt_start(+) and dt.dt_end(+);

Open in new window


left outer join
for a left (outer) join besides all the matching rows in the condition, all the rows in the left not matching the condition are also added to the result, but with null as value for all the fields of the outer joined table(s)
0
 

Author Comment

by:soccerplayer
ID: 39930321
I really didn't understand this fully. I posted a similar question before and got responses but still don't understand why this filter is different from a join filter. I am extremely familiar with all kinds of joins but this doesn't fall in that category.


I'll look further later.
0
 
LVL 10

Expert Comment

by:dbmullen
ID: 39932677
if you question is about the "(+)"
then:
select mt.id, mt.data, mt.dt, dt.id detail_id, dt.data detail_data, dt.dt_start, dt.dt_end
from mt, dt
where mt.dt between dt.dt_start(+) and dt.dt_end(+);

is the same as saying this SYNTAX:
select mt.id, mt.data, mt.dt, dt.id detail_id, dt.data detail_data, dt.dt_start, dt.dt_end
from mt left outer join  dt
        on mt.dt >= dt.dt_start
       and mt.dt <= dt.dt_end
;

the (+) is just a way for Oracle to do "non-inner" joins but "ansi" syntax is valid in oracle.  some people just like to use the "(+)" syntax

since you are familiar with "all kinds of joins" I'm assuming your question is NOT about how a LEFT join works....  if that is your question, then with the query above.
1)  give me everything from table MT
2)  if you happen to find a matching row on the DT table, show me that information
3)  if you don't find a match, return a NULL
0
 

Author Comment

by:soccerplayer
ID: 39935703
Can you please take a look at this question:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28246372.html

I had asked this and someone explained this with two tables. However, the answer does not make sense to me now. If you are saying that:
WHERE vDate between ColBeginDate(+) AND ColEndDate(+)

means vDate >= ColBeginDate AND vDate <= ColEndDate
then I'll have to try it out but the intention of the coder seems to be what you are saying.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39937578
Please, do us all and yourself a favour and stop using (+) if it is confusing for you. There is absolutely no reason to have it anywhere, and usually you've a hard time to decipher what the conditions really mean. Using (+) in a between is one of the worse examples.

The translation of dbmullen is correct. The accepted answer in the referenced question is correct, too.

I always read (+) as "might also be null", which makes it more simple for me.
In general, all columns of the "nullable" table of an outer join need to be "(+)'d" in the join condtion, else you are forcing an inner join. That simple it is.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now