Since Oracle 9i, Oracle SQL supports the ANSI SQL syntax. It takes a bit of getting used to, especially when you are familiar with the Oracle syntax, but it is much more verbose, self-documenting, if you will.

Syntax

Part of the Select syntax is the joining of tables. To join two tables (or views) you have two options. An inner join (both tables should contain the records) or an outer join (records in one or both tables are optional).

For this article we will need two tables to show the different methods of joining.

Table A

Key

Value

Type

1

Apple

1

2

Pear

1

3

Orange

1

4

Lettuce

2

5

Spinach

2

6

Hamburger

3

7

Steak

3

Table B

Key

Value

1

Fruit

2

Vegetable

3

Meat

4

Fish

Cross Join

A cross join, also called a Cartesian join, is made in Oracle SQL by omitting a join predicate.

SELECT *
FROM a
,b

If you encounter something like this you are most likely thinking that a join predicate is missing. If you use ANSI SQL you write this as follows:

SELECT *
FROM a
CROSS JOIN b

Implicitly indicating that a Cartesian join is intended. Both approaches give you the same result, but the ANSI version is more self-documenting. In the first version you should actually add a comment indicating that.

Natural Join

When you use natural join you instruct the RDBMS to join on all columns that have the same name. Columns in both tables must have the same value to satisfy the join condition. In the example provided, the query:

SELECT *
FROM a NATURAL
JOIN b

doesn’t return any results since there are no values which are the same in both tables.

Join

There are actually two flavors in this:
JOIN…USING
and
JOIN…ON
When using the first version, you mention the column names to use in the join condition. If you want to use columns in both tables that have the same name, but not all of them (like the natural join) you could execute a query like this:

SELECT *
FROM a
JOIN b
USING (key)

This way the tables are joined just using the key column to satisfy the condition, whereas the natural join would also take the value column into account.

If the column names to be used in the join condition are different in both tables (which is most common, I think) then you would want to use the JOIN…ON syntax.

SELECT *
FROM a
JOIN b ON (a.type = b.key)

This way you tell the SQL engine exactly which conditions to use for the join. The names can be different in both tables.

Left/Right Outer Join

One thing that takes the most getting used to is the outer join. In Oracle SQL you are probably used to writing something like this:

SELECT *
FROM a
,b
WHERE a.type(+) = b.key

where the (+) indicates that the values don’t need to exist in the table to get a row in the result set. If you put the (+) sign on the other side of the equation you indicate that rows in the other table are optional.

SELECT *
FROM a
,b
WHERE a.type = b.key(+)

In ANSI SQL you use the LEFT or RIGHT OUTER JOIN syntax.

SELECT *
FROM a
RIGHT OUTER JOIN b ON (a.type = b.key)

and

SELECT *
FROM a
LEFT OUTER JOIN b ON (a.type = b.key)

What is most confusing at first is when the (+) is on the left of the equation, you should use a RIGHT OUTER JOIN and vice versa. What this syntax says, in plain English, is: FROM a LEFT OUTER JOIN b i.e. Take the table on the LEFT of this command and join its rows with rows in the table on the right if they exist. If they don’t then just return the values from the table on the left, adding NULL values for the columns that should come from the table on the right.

Full Outer Join

A thing that can be ‘easily’ accomplished using ANSI SQL is the FULL OUTER JOIN, meaning you want the result set to contain all the records from both tables, and if a corresponding record doesn’t exist in the other table, just add NULL values for the missing columns.

SELECT *
FROM a
,b
WHERE a.type(+) = b.key(+)

This syntax is unfortunately not supported by Oracle SQL. You could mimic this behavior by using a left outer join UNIONed by a right outer join:

SELECT *
FROM a
,b
WHERE a.type(+) = b.key
UNION
SELECT *
FROM a
,b
WHERE a.type = b.key(+)

Using ANSI SQL this can be done in a single statement

SELECT *
FROM a
FULL OUTER JOIN b ON (a.type = b.key)

On two simple tables the union can easily be used, but imagine your real world query consisting of dozens of tables where just a couple should be outer joined both ways. Copy-Paste will come in handy here, but think about a change in the specification. Chances are you will forget to implement the change in one of the queries.

Conclusion

If you are not yet using ANSI SQL, I think you should make yourself familiar with it as soon as possible. Your SQL will be vendor independent (like you would ever want to move away from Oracle) and your statements are more self-documenting, relieving you of the duty of documenting your statements. Another thing is it’s easier to explain to your Java or PHP (or…) developing colleagues when you are using ANSI SQL. It’s likely that they are more familiar with the ANSI SQL syntax, since it can also be used in, for instance, MySQL.

Patrick Barel is a PL/SQL Developer for AMIS Services in the Netherlands and an Oracle ACE. Besides working with SQL and PL/SQL did he co-develop CodeGen and wrote different plug-ins for PL/SQL Developer. He publishes articles on the AMIS Technology blog and on his own blog.

You cannot "mimic" a true FULL OUTER JOIN using Oracle's join syntax with a UNION. The problem is that the UNION returns distinct rows. Using UNION ALL would return duplicates that also don't exist in FULL OUTER JOIN. Using your example tables, say that I'm interested in looking at the a.type, b.key, and b.value columns.

Insert Into Delme
Values('101','Ankur')
/
Insert Into Delme
Values('102','Kumar')
/
Insert Into Delme
Values('103','Thakran')
/
Insert Into Delme
Values('104','Danny')
/
Insert Into Delme
Values('105','Danger')
/
Commit
/
Insert Into Sur_Name
Values('101','Ji')
/
Insert Into Sur_Name
Values('102','Sir')
/
Insert Into Sur_Name
Values('103','Boss')
/
Commit
/
The following query returns only one row which is correct
select *
from delme d, sur_name sn
where d.id = sn.id(+)
and d.id = 101
but when the same query is written using the ANSI standard the why the following query is not returning single record.
select *
from delme d
left outer join sur_name sn
on d.id = sn.id
and d.id = 101
but if I write the same query with some alteration then it works fine

select *
from delme d
left outer join sur_name sn
on d.id = sn.id
where d.id = 101
I have read that using the filter condition in the on clause or in the where clause does not make any difference, but here you can see the difference do exists. Sir can you explain in details what should be the correct way to write outer join queries when the involve filer condition and comparison with constant values.
Thanks,
Ankur Kumar

[...] important thing you need to keep in mind while learning queries is to follow the ANSI standard. This will help you in all your projects since, ANSI SQL commands is an internationalized standard [...]

Cary Millsap’s latest book

The Method R Guide to Mastering Oracle Trace Data, Second Edition contains the richest description of Oracle extended SQL trace data that you’ll ever find, and over 100 pages of worked examples, using the software tools built by Cary’s Method R Corporation.