Is nested select clause decreases the database performance??

Question

I used to write select clause in side select clause to avoid joins in from clause. But I am afraid that is it a good coading practice or it will degrade database performance. Below is the query which contains multiple tables but I have written it using nested select clause without any join statement. Please let me know if I am making any mistake or it is ok. At this moment, I am getting accurate result.

SELECT * ,
       (select POrderNo from PurchaseOrderMST POM 
        where POM.POrderID=CET.POrderID)as POrderNo,
       (select SiteName from SiteTRS ST where ST.SiteID=CET.SiteID)as SiteName,
       (select ParticularName from ParticularMST PM where   
        PM.ParticularID=CET.ParticularID)as ParticulerName  
FROM ClaimExpenseTRS CET
WHERE ClaimID=@ClaimID

possible duplicate : http://stackoverflow.com/questions/1067016/join-queries-vs-multiple-queries — Gaurav Shah, Sep 19 '11 at 08:59

score 2 · Accepted Answer · answered Sep 19 '11 at 08:57

I'd use joins for this because it is best practice to do so and will be better for the query optimizer.

But for the learning just try to execute the script with join and without and see what happens on the query plan and the execution time. Usually this answers your questions right away.

score 1 · Answer 2 · answered Sep 19 '11 at 09:02

Your solution is just fine. As long as you are only using 1 column for each "joined" table, and has no multiple matching rows, it is fine. In some cases, even better than joining. (the db engine could anytime change the direction of a join, if you are not using tricks to force a given direction, which could cause performance suprises. It is called query optimiyation, but as far as you really know your database, you should be the one to decide how the query should run).

score 0 · Answer 3 · answered Sep 19 '11 at 08:53

0

I think you should JOIN indeed. Now your creating your own JOIN with where and select statements.

answered Sep 19 '11 at 08:53

Max

1,068
1
10
15

JOIN is non-optimal solution. and not recommended – Gaurav Shah Sep 19 '11 at 08:56
So implementing your own join statement is better then the build in? – Max Sep 19 '11 at 08:58
I hope Gaurav Shah is joking, although there is no indication that he is :( – Jacob Sep 19 '11 at 08:59

Is nested select clause decreases the database performance??

3 Answers3