Reputation: 19193

MySQL JOIN query

Having the following SQL

SELECT 
    u.user_id     AS user_id, 
    u.user_name   AS user_name,
    d.user_data   AS user_data 
FROM 
    tbl_users u, 
    tbl_data  d        
WHERE 
    d.user_id = u.user_id

Does this still produces a JOIN (as there's no JOIN keyword in this sql)? How does MySQL handle this query?

And is there any difference if the WHERE clause would be u.user_id = d.user_id instead of d.user_id = u.user_id ?

Thanks.

Upvotes: 1

Answers (4)

Björn

Reputation: 29381

Yes, this implicitly tells MySQL to do an inner join, it's equivalent to:

SELECT 
    u.user_id     AS user_id, 
    u.user_name   AS user_name,
    d.user_data   AS user_data 
FROM 
    tbl_users u
    INNER JOIN
        tbl_data  d
    ON
        d.user_id = u.user_id

Upvotes: 1

Jordan

Reputation: 32522

What you're writing is the old style SQL syntax before ANSI standardized the JOIN keywords back in 1992.

With a simple query like what you're writing above, most RDBMS's will internally convert it to the same lookup method as an inner join.

As for the order of the columns in the where clause, the only time it matters is when you have a derived column, or a function. Generally you would want to keep the non-derived or non-functional column first, in that case, so that the engine can figure out the correct index to use.

Upvotes: 3