TIM HALL extract - CROSS JOIN

what is the advantage of CROSS JOIN over the normail cartesian product.

below is the plan of two statements ( from Tims's site ) . both shows the same plan.

Quote:

---

12:09:37 SQL> SELECT first_name,last_name, department_name FROM employees CROSS JOIN departments;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=2889 Bytes=7
8003)

1 0 MERGE JOIN (CARTESIAN) (Cost=28 Card=2889 Bytes=78003)
2 1 TABLE ACCESS (FULL) OF 'DEPARTMENTS' (Cost=1 Card=27 Byt
es=324)

3 1 BUFFER (SORT) (Cost=27 Card=107 Bytes=1605)
4 3 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=107 By
tes=1605)




12:09:43 SQL> SELECT first_name,last_name, department_name FROM employees, departments;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=2889 Bytes=7
8003)

1 0 MERGE JOIN (CARTESIAN) (Cost=28 Card=2889 Bytes=78003)
2 1 TABLE ACCESS (FULL) OF 'DEPARTMENTS' (Cost=1 Card=27 Byt
es=324)

3 1 BUFFER (SORT) (Cost=27 Card=107 Bytes=1605)
4 3 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=107 By
tes=1605)

---

Hi,

How many rows are there in both the tables.What I feel is when there is large amount of data then you would able to find the diffrence between both the queries.

Take Care.

both tables are the same created by sample schemas in Oracle 9i Release 1.

any way, these are from HR schema

employees - 107 records
deparments - 27 records

did you tried it ?

Hi,

No I didnt try but it would reflect the diffrence after having used for queries retriving large amount of data.

Take Care.

There is no difference as far as I can tell. The point of a CROSS JOIN is to explicitly state that you are trying to get a cartesian product. That way people don't think you've made a mistake and try to fix it.

Oracle claim that there are no performance differences between the traditional syntax and the ANSI syntax.

The only time there is a difference is when using the FULL OUTER JOIN since this is not possible using the non-ANSI syntax.

Note. Please don't start quoting examples of SQL with UNIONs. These are not examples of FULL OUTER JOINs, although they can be used to return the same data set that a FULL OUTER JOIN would.

Cheers

Hi,

but there is no performance increase compared to the existing syntax

From Tim's Site...

Take Care.

Quote:

---

Originally posted by TimHall

Note. Please don't start quoting examples of SQL with UNIONs. These are not examples of FULL OUTER JOINs, although they can be used to return the same data set that a FULL OUTER JOIN would.

Cheers

---

can u explain it in detail .

to start with union you have to run more than one time the same (or similar) query, with joins you DONT
You cannot compare unions with joins man

Quote:

---

Originally posted by sjadhavdba
what is the advantage of CROSS JOIN over the normail cartesian product.

---

Cross join is the equivalent of a Cartesian product. The only difference is that before 9i we could only implicitely ask for the Cartesian product of two tables by listing the tables in the FROM clause and then we had to fix the mess by using the WHERE clause. Now, full SQL:1999 joint syntax is being implemented and the join is given where it should be.

I am just curious: is there a DBA who knows or is interested in infinite Cartesian products?

Sounds too heavy for me!

http://www.google.com/search?hl=en&l...=Google+Search