SQL & PL/SQL

Maybe these examples will give you an idea...

SQL> select * from t1;

        ID
----------
         1
         2
         3
         4

SQL> select * from t2;

        ID
----------
         3
         4
         5
         6

-- LEFT OUTER JOIN
SQL> select t1.id, t2.id
  2  from t1 LEFT OUTER JOIN t2 ON (t1.id = t2.id);

        ID         ID
---------- ----------
         3          3
         4          4
         1
         2

-- RIGHT OUTER JOIN
SQL> select t1.id, t2.id
  2  from t1 RIGHT OUTER JOIN t2 ON (t1.id = t2.id);

        ID         ID
---------- ----------
         3          3
         4          4
                    6
                    5

-- LEFT JOIN (SAME AS LEFT OUTER JOIN)
SQL> ed
Wrote file afiedt.buf

  1  select t1.id, t2.id
  2* from t1 LEFT JOIN t2 ON (t1.id = t2.id)
SQL> /

        ID         ID
---------- ----------
         3          3
         4          4
         1
         2

-- RIGHT JOIN (SAME AS RIGHT OUTER JOIN)
SQL> ed
Wrote file afiedt.buf

  1  select t1.id, t2.id
  2* from t1 RIGHT JOIN t2 ON (t1.id = t2.id)
SQL> /

        ID         ID
---------- ----------
         3          3
         4          4
                    6
                    5

-- INNER JOIN (REGULAR JOIN)
SQL> ed
Wrote file afiedt.buf

  1  select t1.id, t2.id
  2* from t1 INNER JOIN t2 ON (t1.id = t2.id)
SQL> /

        ID         ID
---------- ----------
         3          3
         4          4

-- FULL OUTER JOIN
SQL> ed
Wrote file afiedt.buf

  1  select t1.id, t2.id
  2* from t1 FULL OUTER JOIN t2 ON (t1.id = t2.id)
SQL> /

        ID         ID
---------- ----------
         3          3
         4          4
         1
         2
                    6
                    5

6 rows selected.

SQL>

cross join

SQL> with 
  t1 as (select column_value id from table(sys.odcinumberlist(1,2,3,4))),  
  t2 as (select column_value id from table(sys.odcinumberlist(3,4,5,6)))  
select * from t1 cross join t2

        ID         ID
---------- ----------
         1          3
         1          4
         1          5
         1          6
         2          3
         2          4
         2          5
         2          6
         3          3
         3          4
         3          5
         3          6
         4          3
         4          4
         4          5
         4          6

and FULL NATURAL JOIN

select * from t1 full natural join t2

        ID
----------
         3
         4

LOL

well, a natural full outer join would be less meaningless, but I do not recommend using natural join as specifying the keys is a better practice

Very funny Laurent. ;)

Seriously though, does anybody actually use the CROSS JOIN syntax? It's just a bloomin' cartesian product at the end of the day. I suppose it could be useful from a documentation point of view as it would be a clear indication that the cartesian product was deliberate (assuming the developer knows what they're doing).

Agree about the natural joins, definitely bad practice, even on the best designed databases.

cross join is useful :

with 
  t1 as (select rownum id, column_value x from table(sys.odcinumberlist(100,200))),  
  t2 as (select column_value y from table(sys.odcinumberlist(300,400))),
  t3 as (select column_value id from table(sys.odcinumberlist(2,3)))
select * from t1 cross join t2 full join t3 on (t1.id=t3.id)

        ID          X          Y         ID
---------- ---------- ---------- ----------
         1        100        300           
         1        100        400           
         2        200        300          2
         2        200        400          2
                                          3

but I do not use id every day 8-)

SQL statements revolve around TABLEs. That is, for each record in a TABLE that the query refers to, the engine evaluates there WHERE clause, and if it returns TRUE, the SELECT list is returned.

The FROM list is processed geometrically. That is, if there is one TABLE, it does one evaluation for each record, if there are two TABLEs, it evaluates the first record of the first TABLE for each record in the second TABLE, then it evaluates the second record in the first TABLE for each record in the second TABLE. Wash, rinse, repeat. Same for three TABLEs and so on. This operation is known as a JOIN. Specifically, this is called a Cartesian JOIN.

Cartesian joins are usually useless. They return the same records many times, and this is rarely what the user wants. The user usually wants there to be some association between the the TABLEs in the FROM clause. And this is accomplished by using a WHERE clause (or the ANSI standard's JOIN clause where supported) that ties the two TABLEs together. If such a WHERE clause is used, only the relevant records in both TABLEs are returned. This is called an INNER JOIN.

INNER JOINs restrict both TABLEs to records that satisfy the WHERE clause. Sometimes, the user wants all the data from one TABLE, but only matching records in the second TABLE. This is called an OUTER JOIN. There are always two (sets of) TABLEs in an OUTER JOIN, one is too return all records, the other, just the matching records. To identify which TABLE is which, they are called RIGHT OUTER JOINs or LEFT OUTER JOINs. The RIGHT or LEFT refers to which side of the join operator is the TABLE that returns every record. The default is LEFT.

A FULL OUTER JOIN returns all records from both TABLEs, matching up the ones which match. (This is not possible in the WHERE clause.)

Just a small correction to the wording if I may...
They don't return the same records many times, they actually return all possible combinations of records from the cartesianally joined tables. The actual returned records are all different. It is rarely what the user wants because data is often stored in a database in a relational fashion and the cartesian product returns results that imply there is no specific relationship between two sets of data hence it looks as if everything is related to everything.
There is no default. LEFT or RIGHT must be specified. It is only the "OUTER" keyword that is optional. e.g.

SQL> select t1.id, t2.id
  2  from t1 OUTER JOIN t2 ON (t1.id = t2.id);
from t1 OUTER JOIN t2 ON (t1.id = t2.id)
                          *
ERROR at line 2:
ORA-00904: "T1"."ID": invalid identifier


SQL> select t1.id, t2.id
  2  from t1 LEFT OUTER JOIN t2 ON (t1.id = t2.id);

        ID         ID
---------- ----------
         3          3
         4          4
         1
         2

SQL> select t1.id, t2.id
  2  from t1 LEFT JOIN t2 ON (t1.id = t2.id);

        ID         ID
---------- ----------
         3          3
         4          4
         1
         2

SQL>

;)

First of all, thank you for the correction! :)

On the Cartesian join, i meant the same record of each TABLE would be repeated. Silly me leaving out the most important point. Heh.

As for the OUTER JOINs, i was very wrong, that's what happens when i don't check the documentation before posting!

Hi,

Ofcourse we use CROSS JOINS. Its being used when we need populate table data for a period which u may not have data for it. A Simple example.

Lets say we get sales data for various region for various months and the report needs for next 12 month data for every region from now. its easy to go ahead with a cross join with Date and Sales Data tables. Oracle Never gives out an option without reason

This may be a late reply to you

As someone resurrected this old thread I'd like to point out to [url http://www.gplivna.eu/papers/sql_join_types.htm]my article SQL join types, which contains classified join types along with quite many examples, attempts to visualise them ;) as well as some additional info.
I doubt it will help OP, but probably someone will find new info there.

Gints Plivna
http://www.gplivna.eu

classified join types along with quite many examples,
attempts to visualise them ;) as well as some
additional info.
Jeez man, you must have been bored to write all of that, or were you being paid to rewrite the oracle manuals?

;)

Agree about the natural joins, definitely bad
practice, even on the best designed databases.

Probably you're ignoring cases like this where natural join is very helpful.

select owner,table_name,column_name,r_owner,r_table_name,r_column_name
from (
		select owner,constraint_name,r_owner,r_constraint_name
		from dba_constraints
		where constraint_type = 'R'
	) natural join (
		select owner,constraint_name,table_name,column_name, position
		from dba_cons_columns
	) natural join (
		select owner as r_owner,constraint_name as r_constraint_name,
			table_name as r_table_name,column_name as r_column_name ,position
		from dba_cons_columns
	)

Bye Alessandro

Not a single cent :P

At first I wanted to draw a meta model of joins. Because usually people speak about these all natural, inner, outer, whatever else joins without understanding how they relate to each other. I.e. in many articles you can find that natural joins are only inner joins, or self joins are only inner joins or whatever else wrong assumptions. So my idea was to clear up all this mess and at first understand all this stuff for myself. Then came the idea to put it somewhere on web. For me it was OK but for other people who probably didn't know all the details, examples would be helpful. Also I tried all of them on MySQL and SQL Server to understand what the difference is. So it emerged as it is now :)
And BTW show me where in Oracle manuals there are joins classified and such a nice picture? ;) Where are exactly explained the difference between join conditions and where conditions for outer joins? Where are explained what work and what not on SQL Server and MySQL? :)

Gints Plivna
http://www.gplivna.eu

Oh yea and speaking about the join visualisation - I was a bit shocked how joins where visually "explained" [url http://www.codinghorror.com/blog/archives/000976.html]using venn diagramms here. I was thought that Venn diagramms could symbolize sets and set operations. And not in any kind joins. So I absolutely don't like this explanation. On the other hand I thought how I can offer something I like more :) And so the result is in my article...

Gints Plivna
http://www.gplivna.eu

Well data on a database is sets and joins are the joining of sets of data, so joins on that data can be represented with venn diagrams. Looked ok to me.

I thought Oracle's documentation on joins was ok myself although it doesn't have a diagram to show a conceptual model of them. It does give examples and explanations which are sufficient for anybody who at least understands the basics of database concepts.

Each to their own I guess.

Aha the question only remains how for example Inner join as it is explained there looks the same as usually Intersect is explained using venn diagrams.
As well as union, except and symmetric difference... But OK there are enough comments there explaining this :)

Gints Plivna
http://www.gplivna.eu

It is true. But not totally true. Venn diagrams are fine only to show operation that give part of their input as output result so you can highlight the output among the input sets.

Union, intersect and minus return rows contained in at least one of the sets involved in the operation and it looks to show how they work.

But the rows returned by a join can't be found in the sets involved in the operation because usually a join returns rows of a different type. Venn diagrams mostly show the division ( http://en.wikipedia.org/wiki/Relational_algebra#Division ) of a join by each input set, but the relation between those diagrams and the join operator it's not that clear if you don't consider to apply division as well after the join.

In my opinion a join needs a diagram that can show an output with a different domain than input and the diagrams used in the article satisfy this requirement.

Bye Alessandro

Give me answer please.

You want someone to read the thread you have posted to for you?

Many thanks to gintsp for his article on join types. It answered my nagging questions on join condition placement for outer joins.
Brian

please tell me what are the left inner joins and right inner joins in oracle.