Outer join with oracle SQL

An outer join use a (+) on the side of the operator where we want to have nulls returned if no value matches.
Create table data_usage (
c_time date,
m_time date,
V varchar2(20),
name varchar2(20),
host varchar2(20)
)
Insert into data_usage values (sysdate, sysdate, ‘SSN’, ‘SCOTT’, ‘D1’);
Insert into data_usage values (sysdate, sysdate+1, ‘SSN’, ‘SCOTT’, ‘D1’);
Insert into data_usage values (sysdate, sysdate+2, ‘SSN’, ‘SCOTT’, ‘D1’);
Insert into data_usage values (sysdate, sysdate, ‘SSN’, ‘TOM’, ‘D2’);
Insert into data_usage values (sysdate, sysdate+ 0.5, ‘SSN’, ‘TOM’, ‘D2’);
Insert into data_usage values (sysdate, sysdate+1.5, ‘SSN’, ‘TOM’, ‘D2’);
Insert into data_usage values (sysdate, sysdate, ‘SSN’, ‘MIKE’, ‘D3’);
Insert into data_usage values (sysdate, sysdate, ‘SSN’, ‘DAVID, ‘D3’);
Create table users (
Name varchar2(20),
Host varchar2(20)
)

Insert into users values ( ‘SCOTT’, ‘D1’);
Insert into users values ( ‘TOM’, ‘D2’);
Insert into users values ( ‘SCOTT’, ‘D2’);
Insert into users values ( ‘MIKE’, ‘D3’);
Insert into users values ( ‘DAVID’, ‘D1’);
Insert into users values ( ‘SAM’, ‘D2’);
Insert into users values ( ‘MIKE’, ‘D1’);

If we want rows without null value.

Select u.name, u.host, d.m_time, d.v
From users u, data_usage d
Where u.name = d.name
And u.host = d.host;

NAME HOST M_TIME V
-------------------- -------------------- ----------- --------------------
SCOTT D1 31-AUG-2010 SSN
SCOTT D1 01-SEP-2010 SSN
SCOTT D1 02-SEP-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 01-SEP-2010 SSN
MIKE D3 31-AUG-2010 SSN

If we want null for users table, both u.name and u.host need associate with an (+).
Select u.name, u.host, d.m_time, d.v
From users u, data_usage d
Where u.name(+) = d.name
And u.host(+) = d.host
;

NAME HOST M_TIME V
-------------------- -------------------- ----------- --------------------
SCOTT D1 02-SEP-2010 SSN
SCOTT D1 01-SEP-2010 SSN
SCOTT D1 31-AUG-2010 SSN
TOM D2 01-SEP-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 31-AUG-2010 SSN
MIKE D3 31-AUG-2010 SSN
31-AUG-2010 SSN

If we want null for data_usage table.

Select u.name, u.host, d.m_time, d.v
From users u, data_usage d
Where u.name= d.name(+)
And u.host = d.host(+)
;
NAME HOST M_TIME V
-------------------- -------------------- ----------- --------------------
SCOTT D1 31-AUG-2010 SSN
SCOTT D1 01-SEP-2010 SSN
SCOTT D1 02-SEP-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 01-SEP-2010 SSN
MIKE D3 31-AUG-2010 SSN
SAM D2
MIKE D1
SCOTT D2
DAVID D1

ATTN: (+) can not be added to both side of an relation for outer-joined table

Let’s put date in the select

If we want rows without null value.

Select u.name, u.host, d.m_time, d.v
From users u, data_usage d
Where u.name = d.name
And u.host = d.host
And d.m_time > sysdate + 0.2;

NAME HOST M_TIME V
-------------------- -------------------- ----------- --------------------
SCOTT D1 02-SEP-2010 SSN
SCOTT D1 01-SEP-2010 SSN
TOM D2 01-SEP-2010 SSN
TOM D2 31-AUG-2010 SSN
If we want null for users table, both u.name and u.host need associate with an (+).
Select u.name, u.host, d.m_time, d.v
From users u, data_usage d
Where u.name(+) = d.name
And u.host(+) = d.host
And m_time > sysdate - 1
;

NAME HOST M_TIME V
-------------------- -------------------- ----------- --------------------
SCOTT D1 02-SEP-2010 SSN
SCOTT D1 01-SEP-2010 SSN
SCOTT D1 31-AUG-2010 SSN
TOM D2 01-SEP-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 31-AUG-2010 SSN
MIKE D3 31-AUG-2010 SSN
31-AUG-2010 SSN

If we want null for data_usage table, the m_time need a (+) too.

Select u.name, u.host, d.m_time, d.v
From users u, data_usage d
Where u.name= d.name(+)
And u.host = d.host(+)
And m_time(+) > sysdate
;
NAME HOST M_TIME V
-------------------- -------------------- ----------- --------------------
SCOTT D1 31-AUG-2010 SSN
SCOTT D1 01-SEP-2010 SSN
SCOTT D1 02-SEP-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 01-SEP-2010 SSN
MIKE D3 31-AUG-2010 SSN
SAM D2
MIKE D1
SCOTT D2
DAVID D1