CODEWITHSUNDEEP
mysqlselectjoin
Question Overflow
Question Overflow

Reputation: 11255

Using, On or Where to combine two queries

To speed up certain queries, I have split a large table into two tables (one with fixed row format and another with dynamic row format), each sharing the same primary key and the same total number of rows but containing different columns.

Some queries requires information from both tables. To get the required data in one query, I have the option of doing the following:

SELECT A.col2, B.col3 FROM A INNER JOIN B ON A.col1=B.col1 WHERE A.col1=?

SELECT A.col2, B.col3 FROM A INNER JOIN B USING (col1) WHERE A.col1=?

SELECT A.col2, B.col3 FROM A, B WHERE A.col1=? AND B.col1=?

From the EXPLAIN SELECT statement, I can't see any differences. I would like some advice whether this is really the case.

Upvotes: 0

Views: 78

Answers (1)

raina77ow
raina77ow

Reputation: 106385

The syntax used in the first two queries first appears in the ANSI SQL-92 standard (text link):

 <join specification> ::=
                <join condition>
              | <named columns join>

 <join condition> ::= ON <search condition>
 <named columns join> ::= USING <left paren> <join column list> <right paren>

Basically, USING (col1) means the same as ON a.col1 = b.col1.

Your third query, if updated like...

SELECT A.col2, B.col3 
  FROM A, B 
 WHERE A.col1 = B.col1 AND A.col1=? AND B.col1=?

... is example of 'old-school' JOIN, as that syntax was used before ANSI SQL-92.

Said all that, all three queries should be parsed as equivalent.

Upvotes: 0

Related Questions