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 data开发者_高级运维base 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  

---

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.

---

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

---

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