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: concepts, join
However, ANSI SQL join syntax does not always work in Oracle, at least up to version 10gR2.Comments Off | Filed in Joins, Oracle | Tags: concepts, join
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: concepts, join
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 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 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: concepts, join
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
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…
The Oracle Database Concepts is a great resource not only if you are just getting started with Oracle, but also if you are an experienced Oracle professional and looking to review the basic concepts that are often forgotten or hidden behind more complex topics. The foundation of complexity is simplicity.
While researching a topic related to FIPS (that I’ll blog about soon), I came across this simple but fundamental concept in non other than the Oracle Database Concepts manual: SQL statement execution. The following is a summary of how Oracle processes SQL statements. First the flowchart followed by a brief explanation of each step. Continue reading…1 Comment | Filed in Oracle, Tips | Tags: concepts, parsing, sql
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: concepts, join
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: concepts, join
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: concepts, join
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: concepts, join