Though I study with sql so often, I discovered something very important, today. That is the big difference between subqueries and join statements.
I have two tables named COMPANY and WORK_EXPERIENCE. In Company table there are company_id, company_name, company_country columns and in work_experience table there are id, company_id, employee_id and quit_date columns. So here what I’m trying to do is list all the companies names and the countries where they are in. Also list the number of employees that work in that company beside the name of the company in brackets. Like,

So writing join statement like this:
SELECT dbo.ProperCase([company_name]) + ‘ (’ + cast(count(work_experience.id) as varchar) + ‘)’ COMPANY, COUNTRY_NAME
FROM [COMPANY]
INNER JOIN COUNTRY ON COMPANY.country_id = COUNTRY.country_id
LEFT JOIN WORK_EXPERIENCE ON COMPANY.company_id = WORK_EXPERIENCE.company_id
WHERE quit_date is null
group by COUNTRY_NAME

will bring our list but with an exception. If all rows related to one company in work_experience table have all values different than null in quit_date column, that company won’t be seen in the list. Because that is not what we want we can deal with this complication using subqueries.

SELECT COMPANY_NAME + ‘ (’ +
(select cast(count(WORK_EXPERIENCE.ID) as varchar) from WORK_EXPERIENCE WHERE WORK_EXPERIENCE.company_id = COMPANY.company_id and quit_date is null) + ‘)’ COMPANY, country_name
FROM [COMPANY]
INNER JOIN COUNTRY ON COMPANY.country_id = COUNTRY.country_id

And the main idea is explaining in  :
AllInterview.com
– Majorly subqueries run independently and result of the
subquery used in the outer query(other than corelated subquery).
And in case of join a query only give the result when the
joining condition gets satisfied.