Which is better Sub-Query or JOIN in SQl query?

Question

Can any one suggest which one is better in this case:

1:

SELECT
    COLUMN1, 
        (SELECT 
            COLUMN2 
        FROM
            TABLE2
        WHERE 
            <some condition>) AS COLUMN2
FROM TABLE 1 

2:

SELECT
    T1.COLUMN1,
    T2.COLUMN2
FROM
    TABLE1 T1
INNER JOIN 
    TABLE2 T2 ON <some condition>

Answer 1

There are several axis of "better"...

• Better performance
• Better readability
• Better maintainability

• Better flexibility ....

  So,

  1. Test and find out (examine plans they may be identical)
  2. In the eye of the beholder, where the queries are equvalent I prefer joins
  3. Depends on your staff
  4. Doesn't seem applicable in this case ...

Better is frequently just a trade off...

Answer 2

Just use the analysis tools to optimize your query:

http://www.databasejournal.com/features/mysql/article.php/3925251/Using-MySQLs-Analysis-Tools-to-Improve-Query-Performance.htm

Answer 3

Which is better?

It is not the problem which is better, because they are complete different query.

Your first query will be error if your sub query return more than 1 row.

Answer 4

Use JOIN.

Subqueries vs joins