MySQL: Efficient Queries

Question

Possible Duplicate:
SQL left join vs multiple tables on FROM line?

   SELECT messages.message_title, 
          messages.message_content, 
          messages.message_timestamp, 
          user_message_relations.sender_id
     FROM messages 
LEFT JOIN global_messages ON messages.message_id = global_messages.message_id
LEFT JOIN user_messages ON messages.message_id = user_messages.message_id, user_message_relations
    WHERE user_message_relations.receiver_id = 3

OR

SELECT messages.message_title, 
       messages.message_content, 
       messages.message_timestamp, 
       user_message_relations.sender_id
  FROM messages,  
       global_messages, 
       user_messages, 
       user_message_relations
 WHERE user_message_relations.receiver_id = 3

My main question is, what's the point of using LEFT OUTER JOIN (or any kind of JOIN) if I can just call the table directly like the second query? Is there a benefit?

I see that the second method is not considered "best practice" ... with that in mind, would this following query be correct if I wanted to populate the inbox of a user with the id of 3?

SELECT messages.message_id, 
    messages.message_title, 
    messages.message_content, 
    messages.message_timestamp, 
    user_messages.message_id, 
    user_message_relations.sender_id
FROM user_message_relations 
INNER JOIN user_messages ON user_message_relations.user_message_id = user_messages.user_message_id
INNER JOIN messages ON user_messages.message_id = messages.message_id
WHERE user_message_relations.receiver_id = 3

No, the question is simply: why use any kind of JOIN when you can just call the tables directly? And the answer seems to be in the link you referenced. — dcolumbus, May 28 '11 at 02:32
There's no such thing as "call the tables directly" - you use JOINs, either ANSI-89 or ANSI-92 syntax, to correlate records between tables -- otherwise you have a cartesian product. ANSI OUTER JOINs are only supported in ANSI-92 syntax, barring database specific OUTER JOIN syntax. — OMG Ponies, May 28 '11 at 02:35
Well, there is such a thing because the second query doesn't use any JOINs and it works just fine. Let me add to my question... — dcolumbus, May 28 '11 at 02:41
"works" is meaningless - The third query does not return identical results to queries 1 or 2. — OMG Ponies, May 28 '11 at 02:55
Neither the 2nd does return same as the 3rd. Missing WHERE clauses. — ypercubeᵀᴹ, May 28 '11 at 06:21

score 1 · Accepted Answer · answered May 28 '11 at 03:21

As OMG Ponies says, there's no such thing as "calling tables" directly. In SQL, multiple tables can be be combined in three ways:

outer join, where you match rows of the tables but allow for cases where one of the tables doesn't have a matching row
Cartesian product, where every row from the first table is combined with every row of the second table (hence "product")
an inner join, where rows are matched in both tables

As it happens, your three examples each has one of these, in the order I listed them. None is more "efficient" than the other; they return very different results!

The confusion comes because there are two different syntaxes for these; your example 2 is using the older SQL89 syntax in which table names are separated by commas, and the combination is a Cartesian product unless something in the WHERE clause says to join them.

I see. Thanks Dan! Your statement: "None is more 'efficient' than the other; they return very different results!" is exactly what I was asking. It really is possible for someone to just answer the question without being a complete smart-ass. — dcolumbus, May 28 '11 at 06:29

score 0 · Answer 2 · answered May 28 '11 at 02:30

The left outer join will give you all elements from the table in the LEFT even if there are no matching elements in the right table. The comma operator is a INNER JOIN alias which indicates that you only want elements that match on certain fields (from the query you are posting since you have no limiting factors between the tables then you will end up with a cartesian product of the tables).

This query will ONLY get the tuples where field is the same in Table1 and Table2

Table1 LEFT OUTER JOIN Table2 ON(Table1.field = Table2.field)

This query will get all possible tuples in a cartesian product form

Table1, Table2

See what I added in my question ^ – dcolumbus May 28 '11 at 02:43 — dcolumbus, May 28 '11 at 02:43

MySQL: Efficient Queries

2 Answers2