CODEWITHSUNDEEP
sql-server-2008
Microsoft Developer
Microsoft Developer

Reputation: 5459

Is nested select clause decreases the database performance??

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

Upvotes: 0

Views: 316

Answers (3)

vmatyi
vmatyi

Reputation: 1305

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

Upvotes: 1

Yves M.
Yves M.

Reputation: 3318

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.

Upvotes: 2

Max
Max

Reputation: 1068

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

Upvotes: 0

Related Questions