0

What are the main differences between JOINS(INNER JOIN,LEFT OUTER JOIN, RIGHT OUTER JOIN etc.) and SET operations( INTERSECT, MINUS etc.) ?

Rajeev
  • 442
  • 1
  • 5
  • 18
  • Inners, lefts, etc. Is not a functionality introduced in oracle 9i it exists since the very first version. Oracle follows the [SQL ANSI 92 standards](http://www.iso.org/iso/catalogue_detail.htm?csnumber=16663). – Jorge Campos Sep 30 '15 at 17:01
  • The only similarity I see between joins and operations like intersect and minus is that are sql keywords. I see no similarities at all in their functionality. – Dan Bracuk Sep 30 '15 at 17:14
  • 1
    Your first sentence is false, except in the very broadest sense. – Gordon Linoff Sep 30 '15 at 18:01

1 Answers1

2

First of all, joins and set operations are two different kettles of fish. Joins can allow you to filter rows between sets, it's true, but they're much more than that (you can make a much wider row by joining tables and selecting columns from across all the tables, whereas set operations work on sets of rows which are the same width).

The way I mentally picture things is that joins are horizontal (ie. rows from table1 are next to matching rows from table2, which are next to matching rows from table 3 etc), whereas set operations are vertical (as in the rows from the second set are underneath the rows from the first set). When you would choose to use set operations over joins is highly dependent on the situation. Kind of like deciding whether to use a hammer or a screwdriver to secure two bits of wood together - would you use a hammer to drive a screw into the wood?

Secondly, Oracle has always (well, since at least version 7!) had joins. Joins are fundamental to how relational databases work!

I guess what you're really asking is why did Oracle introduce the ANSI syntax for joins, and I would guess (you'd have to ask Larry for the full reasons!) that it was to bring Oracle in line with the ANSI standards for joins, which many other RDBMS platforms have adopted.

Boneist
  • 22,910
  • 1
  • 25
  • 40