Here is something interesting. Oracle converts this outer join query

SELECT T1.d, T2.c FROM T1, T2 WHERE T1.x = T2.x (+) and T2.y > 5;

and its ANSI equivalent

SELECT T1.d, T2.c FROM T1 LEFT OUTER JOIN T2 ON (T1.x = T2.x) WHERE T2.y > 5;

into this inner join query

SELECT T1.d, T2.c FROM T1, T2 WHERE T1.x = T2.x and T2.y > 5;

That’s according to […]

Jeff Atwood, the author of the Coding Horror blog, has a nice visual explanation of SQL joins. For the non-visual explanation, I refer you to the following articles:

Inner joins Outer joins Cross joins Self joins Equi and non-equijoins Anti-joins and semi-joins

However, ANSI SQL join syntax does not always work in Oracle, at least up to version 10gR2.

I have lost confidence in Oracle’s ANSI style SQL. Eric, in his comment to the post Back to basics: anti-joins and semi-joins, warned me of the following gotcha:

If you are writing queries that select from many tables, like denormalizing data for warehousing, and the sum of the columns in those tables exceeds 1,050, […]

I almost forgot that the Oracle Joins series is not complete yet. So here is the last episode, a quick and easy anti-join and semi-join refresher.

Anti-joins: Anti-joins are written using the NOT EXISTS or NOT IN constructs. An anti-join between two tables returns rows from the first table for which there are no corresponding rows in […]

A self join is a join of a table to itself. This table appears twice (or more) in the FROM clause and is followed by table aliases that qualify column names in the join condition and the SELECT clause. Take for example the employees table, the manager of one employee is also an employee. The […]

The join condition determines whether a join is an equijoin or a non-equijoin. An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns. When a join condition relates two tables by an operator other than equality, it is a non-equijoin. […]

Continuing the series about joins, today I will review outer joins. An outer join extends the result of an inner join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

The […]

Continuing the series about joins, today it’s about inner joins. An inner join (aka simple join) is a join of two or more tables that returns only those rows that satisfy the join condition. When you hear people talking about a “join”, usually they are referring to an “inner join”.

For example:

Am I considered old school because I do not use Oracle’s SQL92 compliant join syntax? After all, it is recommended to follow the standards whenever possible.

Starting with Oracle database version 9i, you can use the SQL join syntax that is compliant with the ANSI/ISO SQL92 standard. You also have the option to continue using the […]

If you execute the following:

select * from dual where dummy(+) in (1,2,3);

you get the following error:

ERROR at line 1: ORA-01719: outer join operator (+) not allowed in operand of OR or IN

However, in Oracle 9i and above you can overcome this restriction by using the ANSI SQL syntax. For example:

select ename, dname from emp left outer […]