0

I've seen answers to other questions on here and most suggest using a JOIN instead of WHERE in SQL.

I'm new to SQL, having started using Oracle around 6 months ago and I've not once used a JOIN.

I don't do any complex queries by any means. A query I use quite often is below. What would the equivalent be using a JOIN, should I do that and are there any advantages?

SELECT orders.no, orders.created, totals.net
FROM orders, totals
WHERE orders.no = totals.no

I'm using an ancient version of Oracle; 8i so I believe some of the JOIN are using (+) or something, but never understood it.

user3838132
  • 31
  • 1
  • 9
  • 1
    You use joins. But the implicit syntax. Using the explicit join syntax you can split the join conditions from the actual filter conditions in the where clause which is more readable. – juergen d Jul 14 '14 at 19:48
  • If you are using oracle 8i, you might not be able to use "join". The fact that you don't understand the plus sign for outer joins is a good reason to join tables in the from clause as opposed to the where clause. – Dan Bracuk Jul 14 '14 at 19:48
  • I think it's just considered cleaner to use ON clauses to distinguish joining conditions from other conditions in the WHERE clause. – Horaciux Jul 14 '14 at 19:48
  • 1
    @MGorgon: That post is about performance – juergen d Jul 14 '14 at 19:50

2 Answers2

0

It's my understanding that the execution plan would be identical and you will get the same result. However using JOIN is cleaner and easier to read. I'm a fan of the old school WHERE myself but I've trained myself to use JOIN instead.

Great thread on it here, including how they are equivalent: Inner join vs Where

Try it yourself, you should get the same execution plan for both.

Community
  • 1
  • 1
Kris Gruttemeyer
  • 872
  • 7
  • 19
0

Well, sometimes it is the other way around: you have to keep using the where join syntax for performance reasons in Oracle.

With very complex sql statements, Oracle still performs better with Oracle joins (aka in the where clause) than with ANSI joins (aka join).

I think there is no real reason to not choose either ANSI joins or Oracle joins. Personally I prefer using ANSI joins though, since it is easier to read / understand.

Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
  • "*Oracle still performs better with Oracle joins*" - that might have been true for 9.x and maybe 10.x, but certainly not for any recent/supported version (11.x, 12.x). Btw: implicit joins in the where clause are "ANSI joins" as well - just an older version of the standard (the `JOIN` operator was introduced in SQL:99 if I'm not mistaken) –  Jul 14 '14 at 21:14
  • @a_horse_with_no_name: We have been experiencing this kind of performance problems in the 10.2 and 11.1 versions of Oracle for our auto-generated system. Some views had joins that had about 20-30 tables. The performance dropped massively when using ANSI joins. Changing the build engine to use Oracle joins fixed that issue. – Patrick Hofman Jul 14 '14 at 21:39
  • I had like to do some test runs on 12 though. Maybe it has improved indeed. Thanks for noting. – Patrick Hofman Jul 14 '14 at 21:45