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]
-----------------------------------------------------------------------------

Reply via email to