msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Is ANSI SQL Join Syntax Your Default Approach to Writing Queries in Oracle?

APC:

I think if Oracle had extended its ANSI SQL support beyond SELECT to the other DML commands the argument over whether to use the new syntax would have died out years ago.

It would have helped for sure. I also think that if you work in an environment where ANSI SQL is not the “standard” it is hard to adopt it as your main approach to writing queries.

Comments Off | Filed in Oracle | Tags: ,


Did You Know This About Outer Joins in Oracle?

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 the Optimizer Development Group blog. They go on to explain:

Here the filter predicate on the outer-joined table T2 does not contain the outerjoin operator (+); thus it will be applied after the left outerjoin has taken place. This will result in the elimination of all null appended rows of T2. Hence, Oracle converts the outer join into an inner join.

And here is an interesting thing about ANSI full outer joins:

Before Oracle 11gR1 all ANSI full outerjoins were converted into a UNION ALL query with two branches, where one branch contained a left outerjoined lateral view and the other branch contained a NOT EXISTS subquery. A native support for hash full outerjoin was introduced in 11gR1 to overcome this problem. When the native full outerjoin cannot be used, Oracle reverts to the pre-11gR1 strategy.

Their latest blog post about Outerjoins in Oracle is a good read.

1 Comment | Filed in Joins, Oracle | Tags: ,


SQL Joins as Seen on a Diagram

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:

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

Comments Off | Filed in Joins, Oracle | Tags: ,


When ANSI SQL Join Syntax Does Not Work in Oracle

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, you always get ORA-01445. This occurs from 9i through 10g2, and has happened often enough to me that I avoid my preference for ANSI syntax on Oracle databases, unless I’m just selecting from a few tables.

Well, it just happened to me. Below are two queries. They both select from standard Oracle E-Business Suite (11.5.10) tables. The two queries are identical except that the first uses the ANSI style syntax, and the second uses the Oracle syntax. As you can see below, the query that uses ANSI joins failed miserably with the error:

ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table.

This looks like a bug to me. Interestingly, if you change the SELECT NULL to SELECT COUNT(*), the query executes successfully. Continue reading…

17 Comments | Filed in Joins, Oracle | Tags: ,


Back to basics: anti-joins and semi-joins

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 the second table. In other words, it returns rows that fail to match the sub-query on the right side.

Suppose you want a list of departments with no employees. You could write a query like this:

SELECT   d.department_name
    FROM departments d
MINUS
SELECT   d.department_name
    FROM departments d, employees e
   WHERE d.department_id = e.department_id
ORDER BY department_name;

The above query will give the desired results, but it might be clearer to write the query using an anti-join:

SELECT   d.department_name
    FROM departments d
   WHERE NOT EXISTS (SELECT NULL
                       FROM employees e
                      WHERE e.department_id = d.department_id)
ORDER BY d.department_name;

Semi-joins:
Semi-joins are written using the EXISTS or IN constructs. A semi-join between two tables returns rows from the first table where one or more matches are found in the second table. The difference between a semi-join and a conventional join is that rows in the first table will be returned at most once.

Suppose you want a list of departments with at least one employee. You could write the query like this:

SELECT   d.department_name
    FROM departments d, employees e
   WHERE d.department_id = e.department_id
ORDER BY department_name;

The department name in the query result will appear as many times as the number of employees in it. So, for example if a department has 30 employees then that department will appear in the query output 30 times.

To eliminate the duplicate rows, you could use the DISTINCT or GROUP BY keywords. A more elegant solution is to use a semi-join between the departments and employees tables instead of a conventional join:

SELECT   d.department_name
    FROM departments d
   WHERE EXISTS (SELECT NULL
                   FROM employees e
                  WHERE e.department_id = d.department_id)
ORDER BY d.department_name;

The above query will list the departments that have at least one employee. The department will appear only once in the query output no matter how many employees it has.

Sources and Resources:

2 Comments | Filed in Joins, Oracle | Tags: ,


Back to basics: self joins

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 rows for both are in the same employees table.

To get information about an employee and her manager, you have to join the employee table to itself, thereby treating employees as if it were two separate tables. The following example query uses a self join to return the name of each employee along with the name of the employee’s manager: Continue reading…

7 Comments | Filed in Joins, Oracle | Tags: ,


Back to basics: Equi and non-equijoins

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. A query may contain equijoins as well as non-equijoins.

Equijoins are the most commonly used. An example of an equijoin: Continue reading…

7 Comments | Filed in Joins, Oracle | Tags: ,


Back to basics: outer joins

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 ANSI SQL syntax of an outer join is: Continue reading…

9 Comments | Filed in Joins, Oracle | Tags: ,


Back to basics: inner joins

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: Continue reading…

15 Comments | Filed in Joins, Oracle | Tags: ,


Back to basics: cross joins

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 “old” syntax – that I have been using for years and that I am so used to. But to me, one of the main advantages of using the “new” join syntax is that join conditions are separated from the other filter conditions in the WHERE clause, making the query self explanatory as far as join conditions are concerned.

I have decided to break my habit and start using the “new” syntax in all my queries (except when I’m working against Oracle 8i). To that end, I will be writing a series of posts about the different types of joins along with code examples both in the “old” and the corresponding “new” syntax. Nothing revolutionary, just simple, “back to basics” stuff.

Here are the different types of joins that I’m planning to review: Continue reading…

6 Comments | Filed in Joins, Oracle | Tags: ,