OUTER JOIN in SQL Server

Let's look at the most widely used join, which is OUTER join. I will not focus on what are outer joins but will give how to us ANSI joins to write OUTER join queries.

There are two types of outer joins:
1) Left Outer Join or Left Join: *= symbol is used to implement this kind of join.
2) Right Outer Join or Right Join: =* symbol is used to implement this kind of join.

SELECT E.EMPID, M.EMPID AS ManagerID
FROM HRDETAILS.EMP E LEFT OUTER JOIN HRDETAILS.EMP M
ON E.MGRID = M.EMPID;

The above query is based on ANSI joins. Now let's rewrite this query using a WHERE clause–based join syntax.
It's very simple just replace "LEFT OUTER JOIN" with "," and then where ever you have ON condition replace that with either WHERE or AND condition depending on the conditions and joins in your query.

SELECT E.EMPID, M.EMPID AS ManagerID
FROM HRDETAILS.EMP E ,
HRDETAILS.EMP M
WHERE E.MGRID *= M.EMPID;

There will not be any difference in the out put.