Will Ansi Join Vs. Non-Ansi Join Queries Perform Differently

Will ANSI JOIN vs. non-ANSI JOIN queries perform differently?

The two queries are the same, except the second is ANSI-92 SQL syntax and the first is the older SQL syntax which didn't incorporate the join clause. They should produce exactly the same internal query plan, although you may like to check.

You should use the ANSI-92 syntax for several of reasons

• The use of the JOIN clause separates
  the relationship logic from the
  filter logic (the WHERE) and is thus
  cleaner and easier to understand.
• It doesn't matter with this particular query, but there are a few circumstances where the older outer join syntax (using + ) is ambiguous and the query results are hence implementation dependent - or the query cannot be resolved at all. These do not occur with ANSI-92
• It's good practice as most developers and dba's will use ANSI-92 nowadays and you should follow the standard. Certainly all modern query tools will generate ANSI-92.
• As pointed out by @gbn, it does tend to avoid accidental cross joins.

Myself I resisted ANSI-92 for some time as there is a slight conceptual advantage to the old syntax as it's a easier to envisage the SQL as a mass Cartesian join of all tables used followed by a filtering operation - a mental technique that can be useful for grasping what a SQL query is doing. However I decided a few years ago that I needed to move with the times and after a relatively short adjustment period I now strongly prefer it - predominantly because of the first reason given above. The only place that one should depart from the ANSI-92 syntax, or rather not use the option, is with natural joins which are implicitly dangerous.

What is difference between ANSI and non-ANSI joins, and which do you recommend?

both syntaxes usually work without problems, but if you try to add a where condition you will see that with the second one is much simpler to understand which is the join condition and which is the where clause.

1)

  SELECT a.name,
         a.empno,
         b.loc 
    FROM tab a,
         tab b 
   WHERE a.deptno = b.deptno(+)
     AND a.empno = 190;

2)

         SELECT a.name,
                a.empno,
                b.loc 
           FROM tab a,
LEFT OUTER JOIN tab b 
             ON a.deptno = b.deptno
          WHERE a.empno = 190;

Also, it's much easier to recognize an outer join and do not forget to include the (+). Overall you can say it's just a question of taste, but the truth is that the second syntax is much more readable and less prone to errors.

Oracle Joins - Comparison between conventional syntax VS ANSI Syntax

Grouping answers together

1. Use explicit JOINs rather than implicit (regardless whether they are outer joins or not) is that it's much easier to accidently create a cartesian product with the implicit joins. With explicit JOINs you cannot "by accident" create one. The more tables are involved the higher the risk is that you miss one join condition.
2. Basically (+) is severely limited compared to ANSI joins. Furthermore it is only available in Oracle whereas the ANSI join syntax is supported by all major DBMS
3. SQL will not start to perform better after migration to ANSI syntax - it's just different syntax.
4. Oracle strongly recommends that you use the more flexible FROM clause join syntax shown in the former example. In the past there were some bugs with ANSI syntax but if you go with latest 11.2 or 12.1 that should be fixed already.
5. Using the JOIN operators ensure your SQL code is ANSI compliant, and thus would allow a front-end application to be more easily ported for other database platforms.
6. Join conditions have a very low selectivity on each table and a high selectivity on the tuples in the theoretical cross product. Conditions in the where statement usually have a much higher selectivity.
7. Oracle internally converts ANSI syntax to the (+) syntax, you can see this happening in the execution plan's Predicate Information section.

Possible Pitfall in using ANSI syntax on 12c engine

Including a possibility of bug in JOIN in 12c. See here

FOLLOW UP:

Quest SQL optimizer tool rewrites the SQL to ANSI syntax.

ANSI Joins VS NON ANSI joins

It seems that *= is RIGHT OUTER JOIN

(If the following doesn't give the expected results try it with LEFT OUTER JOIN)

select * from 
T t, 
JOIN U u1 ON t.id = u1.id
JOIN C c ON t.curr = c.curr
JOIN I i ON t.instanceId = i.instanceId 
JOIN S s ON i.symbol = s.symbol
JOIN TY ty ON i.type = ty.type
RIGHT OUTER JOIN U u2 ON t.exec = u2.exec;

What are the difference between these two SQL queries?

Both Queries will yield same results but the only difference is in syntax,

1st query uses old syntax for join where you describe the relation in WHERE clause .

2nd Query uses newer ANSI syntax, where relation between tables are defined in ON clause.

Second syntax is preferred though.

Read Here for more information.

Is there an Oracle official recommendation on the use of explicit ANSI JOINs vs implicit joins?

I haven't seen it if there is. The reason for preferring ANSI syntax for outer joins in particular (apart from the non-standarrd, Oracle-specific (+) symbol) is that more outer joins are expressible using the ANSI syntax. The restriction "ORA-01417: a table may be outer joined to at most one other table" applies to (+) outer joins but not to ANSI outer joins. Other restrictions on (+) that do not apply to ANSI outer joins are documented here.

One highly respected Oracle expert actually recommends sticking to the old syntax for inner joins - see Jonathan Lewis's blog. He says there that ANSI joins are transformed to traditional Oracle joins under the covers anyway. I don't agree with him 100% (I prefer ANSI joins myself in general), but would not claim to have a fraction of his knowledge on the topic.

In a nutshell, ANSI outer joins are technically superior to old (+) joins, whereas with inner joins it is more just a matter of style.

What is difference between join syntax in T-SQL

What is difference between that?

AFAIK, both are doing INNER JOIN, First one using a Implicit JOIN syntax whereas the second one using a explicit join syntax

I wouldn't expect any performance difference between them but the second style of query using explicit join syntax is much more recommended over the first one cause it's ease of readability and clears the idea as what you are trying to perform. Also, the first style of writing is a old style join syntax.

Difference between these two SQL queries

Both queries get you the same data.

The second query is the straight-forward way to the problem; get all persons that have no entry in person_log. You can do the same with a NOT EXISTS clause instead of a NOT IN clause. (NOT IN is a bit leaner, but the values you select in the subquery must not be null, for otherwise you see no data at all. I usually perfer IN / NOT IN over EXISTS / NOT EXISTS for their simplicity, but that's a matter of personal preference.

The first query is called an anti join. It is a trick to achieve the same as a NOT EXISTS or NOT IN query on weak database systems that don't implement these methods well. (The reason is that when a new database system is written, the programmers usually put all their effort in joins for they are so important and neglect EXISTS and IN for some time.)

It depends on the DBMS which gets executed fastest, NOT IN, NOT EXISTS or the anti join. The ideal DBMS would get to the same execution plan, no matter which syntax you choose.

The anti join can produce large intermediate results. With a mature DBMS you shouldn't use anti joins for this reason and for mere readability.

---

Related Topics