Subquery in SELECT statement Vs Joins

Question

I am having very large tables and existing queries having long sub queries used in SELECT statement. Will the performance be same if the sub queries are converted to JOINs?

How the below queries are different with respect to performance?

SELECT
e.employee_id,
(
  SELECT department_name
  FROM Department
  WHERE employee_id = e.employee_id) Department_Name
FROM Employee e

Vs

SELECT
e.employee_id,
d.department_name Department_Name
FROM Employee e
JOIN Department d
ON d.employee_id = e.employee_id

Answer 1

As far as I know second option would be better than first option because first option contains the sub query. Normally in sql statement execution, joins are work faster than inner(sub) queries, You can take help of execution plan which is generate by SQL Server. Its matter that how you write your query, SQL Server will always transform it on an execution plan. It would be smart way to generate the execution plan for both queries, definitely you will get the performance result.

For more information you can go through the below link.

1. https://blog.sqlauthority.com/2010/06/06/sql-server-subquery-or-join-various-options-sql-server-engine-knows-the-best/

2. http://www.sqlservice.se/sql-server-performance-death-by-correlated-subqueries/

Answer 2

In most of the scenarios table JOINs with Indexed join columns will give better performance over sub queries. Check the estimated execution plan by selecting both the queries (sub-query & JOIN) and you can see the difference in SSMS.