Re: Where clause and cartesian product

Mark C. Stock wrote:
> "Ed Prochak" <ed.prochak_at_magicinterface.com> wrote in message
> news:1134596216.406251.325420_at_f14g2000cwb.googlegroups.com...
> > Hari wrote:
> >> Hi,
> >>
> >> Im new to databases/Oracle.
> >>
> >> Recently I enrolled in an Oracle certification course. The Oracle
> >> course material says that usage of Where clause is preffered as it
> >> avoids a cartesian product (thus overloading the query). One colleague
> >> in the class said that even if one uses a Where clause in a SQL
> >> statement involving 2 tables, the oracle system first does a cartesian
> >> product of the 2 tables and then applies the where condition the ouput
> >> and displays it to the user (my teacher isnt sure about how it works).
> >> How does it work in reality?
> >>
> >> regards,
> >> Hari
> >> India
> >
> > If you are just now learning SQL for the first time, than I say, don't
> > worry about how ORACLE finds the result set. What the DBMS does
> > internally is not your concern at the moment. Learn the logical
> > operations of SQL and you'll understand better how to use more than
> > just ORACLE.
> >
> > So logically a join is first a cartesian product of the data sets
> > represented by the two tables. the where clause then is a logical
> > filter, eliminating result rows that do not match the conditions. Keep
> > in mind that SQL is a layman's language for performing SET Algebra.
> > Trying to figure out the optimizations at this point is only going to
> > slow you down in the long run.
> >
> > Ed
> >
>
> Ed,
>
> Even if you ignore how a particular database implements the join processing,
> it never will begin with a cartesian product, it will be an actual join
> based on the relational expression provided.
>
> To illustrate, any database that processes the following join will
> 'logically' work with the dataset as shown:
>
> SQL> select *
> 2 from emp join dept using (deptno);

You gave it a join condition "using (deptno);" so the 14 rows you are looking at a result set LOGICALLY AFTER the filter is applied.
>

[]
> 14 rows selected.

You missed my point which is that he needs to learn the logic of what's happening NOT necessarily how a DBMS optimizes it.
>
> if it were working with a cartesian product, it would begin by working with
> a dataset like this:
>
> SQL> select *
> 2 from emp, dept;

Logically the set algrebra starts with this. It is much better to think of it this way rather than trying to predict what the optimizer will do. SQL Server's optimizer will do some queries differently than ORACLE, and even ORACLE's optimizer will do differ plans based on the data and statistics available to it.

So if you are a beginner SQL programmer, FORGET ABOUT HOW THE DBMS OPTIMIZES YOU QUERY. Make sure your query returns the right data first.

[]

> 56 rows selected.
>
> Cartesian implies no join condition, return all possible combinations. Not
> part of the 'logical' (or physical) processing when an actual join condition
> is supplied.
>
> ++ mcs

Better IMHO is to learn it as if the DBMS does the cartesian join first. Beginners should not be thinking about optimization yet. For a Relational DB that is logically what it does.

And yes I understand your point but respectfully disagree on using that as a way to teach SQL.

  Ed Received on Wed Dec 14 2005 - 19:16:05 CST