• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

Trying to understand a WHERE clause statement

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
soccerplayer
Asked:
soccerplayer
  • 6
  • 3
  • 3
  • +3
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
QlemoC++ DeveloperCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
soccerplayerAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
soccerplayerAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
soccerplayerAuthor Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
soccerplayerAuthor Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
Geert GruwezOracle dbaCommented:
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
 
soccerplayerAuthor Commented:
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
 
dbmullenCommented:
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
 
soccerplayerAuthor Commented:
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
 
QlemoC++ DeveloperCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now