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>

The better query is the one that returns the desired result. I guess that there can be some situations where the two queries are interchangeable but certainly not always. The sub query version will fail if you get more than one row back from the sub query for a given condition. — Mikael Eriksson, Oct 27 '11 at 06:24
The better query is the one that returns the desired results in lesser time. — Salman A, Oct 27 '11 at 06:25
The better query is the one that returns the desired results using fewer database resources and fast enough to meet your application's requirements. That said, Smruti is asking whether it is better to solve a given problem with a subquery or a join. The example isn't great (or even really necessary), but the question is clear enough. — ObscureRobot, Oct 27 '11 at 06:36
possible duplicate of [Subqueries vs joins](http://stackoverflow.com/questions/141278/subqueries-vs-joins) — Howli, May 14 '14 at 08:26
Did you try searching here? It looks like the question in this thread is identical to your question: http://stackoverflow.com/questions/2577174/sql-join-vs-subquery — Vorapsak, Oct 27 '11 at 06:21

jmoreno · Answer 1 · 2011-12-27T01:30:51.707

4

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...

edited Dec 27 '11 at 01:30

answered Oct 27 '11 at 07:03

jmoreno

12,752
4
60
91

score 3 · Answer 2 · edited May 23 '17 at 11:45

3

Use JOIN.

Subqueries vs joins

edited May 23 '17 at 11:45

Community

1
1

answered Oct 27 '11 at 06:23

Spencer Ruport

34,865
12
85
147

score 2 · Answer 3 · answered Oct 27 '11 at 06:29

2

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.

answered Oct 27 '11 at 06:29

xdazz

158,678
38
247
274

score 1 · Accepted Answer · answered Oct 27 '11 at 06:40

1

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

answered Oct 27 '11 at 06:40

Kevin

1,232
10
28

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

1:

2:

4 Answers4