1z0-071 oracle exam question which answers is correct?

Which three statements are true about the Oracle join and ANSI join syntax?

1. The Oracle join syntax supports natural joins.
2. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax.
3. The Oracle join syntax supports creation of a Cartesian product of two tables.
4. The SQL:1999 compliant ANSI join syntax supports natural joins.
5. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
6. The Oracle join syntax only supports right outer joins.
7. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.

Answer

The answers the exam expects are:

3) The Oracle join syntax supports creation of a Cartesian product of two tables.
4) The SQL:1999 compliant ANSI join syntax supports natural joins.
7) The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.

3) is interesting, because Oracle (AKA SQL:89 compliant ANSI) join syntax doesn’t have an explicit notation for CROSS joining: we just omit the relevant criteria in the WHERE clause. This makes it too easy to accidentally create Cartesian products, and is one of the arguments in favour of using SQL:99.

This option highlights the problem with such exam questions:

5. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.

Whilst this is generally not true and performance is equivalent, there are edge cases where it is true, mainly related to outer joins. The more recent our version of Oracle the fewer and more edgy those cases become. It’s not an argument for using the older syntax, just something to bear in mind. Find out more.