--

CREATE TABLE FirstTab
(
	 ID INT	
)
GO

INSERT INTO FirstTab VALUES
(1),
(2),
(NULL)
GO

SELECT * FROM FirstTab
GO

CREATE TABLE SecondTab
(
	ID INT	
)
GO

INSERT INTO SecondTab VALUES
(1),
(3),
(NULL)
GO

SELECT * FROM SecondTab
GO


--

--

SELECT COUNT(1) Cnt FROM FirstTab a
INNER JOIN SecondTab b ON a.Id  b.ID
--Note  : INNER JOIN with not equal to join

--

--


So here the output will be 3 as 1  2 , 32 and 31
Cnt
-----------
3

(1 row affected)

Note - NULLs will not participate here in JOIN.

--

--

SELECT COUNT(1) Cnt FROM FirstTab a
LEFT JOIN SecondTab b ON a.Id  b.ID
--Note  : LEFT JOIN with not equal to join

--

--

The output will be 4 here.

Cnt
-----------
4

(1 row affected)

Note - 3 rows for the Inner join and 1 NULL remaining from the left table.

--

--

SELECT COUNT(1) Cnt FROM FirstTab a
RIGHT JOIN SecondTab b ON a.Id  b.ID
--Note  : RIGHT JOIN with not equal to join

--

--


The output will be 4 here.

Cnt
-----------
4

(1 row affected)

Note - 3 rows for the Inner join and 1 NULL remaining from the right table.


--

--

SELECT COUNT(1) Cnt FROM FirstTab a
FULL OUTER JOIN SecondTab b ON a.Id  b.ID
--Note  : FULL OUTER JOIN with not equal to join


--

--


Cnt
-----------
5

The output will be 5. 3 for the inner join + 1 NULL for the left join and 1 for the right join.

--

--

SELECT COUNT(1) Cnt FROM FirstTab a
CROSS JOIN SecondTab b 
WHERE a.Id  b.ID

--

--

This is similar as INNER JOIN. So the output will be 3.

--