MySQL: Efficient Queries [duplicate]

This question already has answers here: Closed 11 years ago.

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,  
       gl开发者_StackOverflow中文版obal_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

---

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.

---

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