This may be some use to you. Most of this stuff has been in SQL Server for a long time because they came late to the party and built to ANSI 92.
Left, Right and Full joins
This is the industry standard syntax which is now supported by Oracle (just in case you ever wondered)
select dname, ename
from emp
right outer join dept
on emp.deptno = dept.deptno
order by dname, ename
See <http://www.oreillynet.com/pub/a/network/2002/04/23/fulljoin.html> and http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2107297. Ansi can outer join more tables (so it says).
You can also do inner joins, which are your straightforward join. I think the left and right say which table is outer joined to in the statement, as in left in this case would be emp. A full outer join outer joins in both directions, meaning you no longer have to union left and right together.
If someone wants to rustle up an example of outer joining to two tables go right ahead.
Some other useful things from ANSI 92 in 9i are replacing DECODE with CASE, NULLIF and COALESCE
CASE:
SELECT cust_last_name
, CASE credit_limit
WHEN 100 THEN ‘Low’
WHEN 5000 THEN ‘High’
ELSE ‘Medium’ END
FROM customers;
(see http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/expressions5a.htm#1033394)
COALESCE
COALESCE (expr1, expr2)
is equivalent to:
CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END
Similarly,
COALESCE (expr1, expr2, …, exprn), for >=3
is equivalent to:
CASE WHEN expr1 IS NOT NULL THEN expr1
ELSE COALESCE (expr2, …, exprn) END
NULLIF:
NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1. You cannot specify the literal NULL for expr1.
The NULLIF function is logically equivalent to the following CASE expression:
CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END
(see http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions85a.htm#1001199)