oracle start_date and end_date


what effect has using (+)  after start_date and end_date when we extract a list of employees with
their relative information where family joining start_date and end date falls in the following condition:

trunc(sysdate) between start_date(+) and end_date(+)

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
That is the old Oracle syntax for an "outer join".

The effect depends on the tables joined.  It will either be a left outer join or a right outer join.

An outer join allows rows to be returned from one table even if matching rows aren't in the other table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
... and the field with (+) is the one which might not exist. Basically the other table (employee?)  will always show its rows, but the one with the dates only if both dates are of today. Kinda strange condition, though.
shmzAuthor Commented:

I need to understand this, I am looking at a pre-existing query written by someone else and need to figure all details:

if I have following records:

employeeid | contact_id | contact_start_date | contact_end_date | employee start_date | employee_end_date



and I use the following where clause :
trunc(sysdate) between employee_start_date(+) and employee_end_date(+)
trunc(sysdate) between contact_start_date(+) and contact_end_date(+)

Q1: this will return all records above right?

Q2: but if I want to return only those records that their contact is current (not expired) then what is the condition?

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You are only selecting that table?
shmzAuthor Commented:
Qlemo, not sure about your question?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Are we talking about a single table here? Because the original question tells different. And a outer join of a single table is rather useless.
slightwv (䄆 Netminder) Commented:
>>Q1: this will return all records above right?
>>Q2: but if I want to return only those records that their contact is current (not expired) then what is the condition?

We have no way to know the answers to these questions.  We do not know your tables, data or requirements.

If you want a copy/paste answer, please post a small test case with tables, sample data and expected results.

Here is a small example of the joins.  Hopefully you have a development database to run it.  If not, I suggest sqlfiddle.

drop table tab1 purge;
create table tab1(mgr number, emp number);

insert into tab1 values(1,1);
insert into tab1 values(1,2);
insert into tab1 values(2,3);
insert into tab1 values(3,4);

drop table tab2 purge;
create table tab2(mgr number);

insert into tab2 values(2);

--without the outer join:
select t1.mgr,emp
from tab1 t1, tab2 t2
where t1.mgr=t2.mgr

--with the outer join:
select t1.mgr,emp
from tab1 t1, tab2 t2
where t1.mgr=t2.mgr(+)

--same outer join using ANSI syntax:
select t1.mgr,emp
from tab1 t1 left outer join tab2 t2 on t1.mgr=t2.mgr

Open in new window

shmzAuthor Commented:
thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.