Yves Goergen <[EMAIL PROTECTED]>
wrote:
I was thinking about what happens when I do an SQL query that names
tables and joins between them that aren't used at all. For example
this:
SELECT m.Sender, m.Subject FROM Message m, MessageTag mt;
Full cartesian product, aka cross-product. For every row in m and every
row in mt you would have one row in the resultset, for a total of M*N
rows. Since you are not outputting any fields from mt, you would just
have each (Sender, Subject) pair duplicated N times.
Does it open and read the table MessageTag at all?
Of course. It has to know at least how many times to duplicate each
(Sender, Subject) pair.
Also, what happens
in a more complex example where a more explicit join is done but
never used:
SELECT m.Sender, m.Subject FROM Message m NATURAL JOIN MessageTag mt;
Some (Sender, Subject) pairs may be repeated (if the corresponding row
in m has more than one matching rows in mt), some would appear only
once, some wouldn't appear at all.
Or:
SELECT m.Sender, m.Subject FROM Message m LEFT JOIN MessageTag mt
USING (MessageId);
Some (Sender, Subject) pairs may be repeated (if the corresponding row
in m has more than one matching rows in mt), some would appear only
once. Every pair would appear at least once, even if there's no matching
row in mt.
Does it impact performance when the join appears in the query or will
the optimiser remove it?
No. The other table affects the result of the query, so the join still
has to be performed.
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
