CROSS JOIN in SQL Server

Let's look at the very rarely used join, which is CROSS join. The simplest ways of implementing the CROSS JOIN is remove the joining conditions on these tables.

So, for an ultra-quick example, let's take our first example from the CROSS JOIN section earlier in the chapter. The ANSI syntax looked like this:

SELECT * FROM CARMODELS CM CROSS JOIN COLORS C;

To convert it to the old syntax, we just strip out the CROSS JOIN keywords and add a comma:

SELECT * FROM CARMODELS CM, COLORS C;

INNER JOIN in SQL Sever

Let's look at the very basic join, which is INNER or EQUI JOIN

SELECT E.*
FROM HRDETAILS.EMP E
INNER 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 "INNER 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.*
FROM HRDETAILS.EMP E ,
HRDETAILS.EMP M
WHERE E.MGRID = M.EMPID;

There will not be any difference in the out put.