Unexpected result of Cross join

Question

These are my tblEmp table and tblDept table(I'm using MS-SQL Server 2012), when I try using a cross join on these two tables it's giving me the result which I didn't expected, just wanted to know why this cross join gives this kind of result, Thank you.

ID  Name  Gender    Salary  Dept_id
1   abc   male      2004    1
2   Tom   female    5004    2
3   Sara  female    29404   2
4   Jim    male     8604    3
5   Lisan   male    2078    1
6   Brad    male    9804    3
7   Diana   female  2095    2
8   Henry   male    28204   2
9   Mark    male    20821   1
10  Miley   female  9456    1
11  Richie  male    8604    NULL
12  Lisan   female  20776   NULL

tblDept

ID  Dept_Name         Location
1   IT                Mumbai
2   HR                Delhi
3   Accounts          London
4   OtherDepartment   NewYork

this is the cross join query and it's output

select Name, Gender, Salary, Dept_Name
from tblEmp 
CROSS JOIN tblDept 
where tblEmp.Dept_id is NULL

OUTPUT

Name    Gender  Salary  Dept_Name
Richie  male    8604    IT
Richie  male    8604    HR
Richie  male    8604    Accounts
Richie  male    8604    OtherDepartment
Lisan   female  20776   IT
Lisan   female  20776   HR
Lisan   female  20776   Accounts
Lisan   female  20776   OtherDepartment

What I expected was something like this

    Name    Gender  Salary  Dept_Name
    Richie  male    8604    NULL
    Richie  male    8604    NULL
    Richie  male    8604    NULL
    Richie  male    8604    NULL
    Lisan   female  20776   NULL
    Lisan   female  20776   NULL
    Lisan   female  20776   NULL
    Lisan   female  20776   NULL

Answer 1

If you do want a NULL row for each department per individual that has a null dept_id e.g.

Name    Gender  Salary  Dept_Name
Richie  male    8604    NULL
Richie  male    8604    NULL
Richie  male    8604    NULL
Richie  male    8604    NULL
Lisan   female  20776   NULL
Lisan   female  20776   NULL
Lisan   female  20776   NULL
Lisan   female  20776   NULL

you could execute this...

select Name, Gender, Salary, NULL AS Dept_Name
from tblEmp 
CROSS JOIN tblDept 
where tblEmp.Dept_id is NULL

Answer 2

If you need show all employees and their departments, you can use LEFT JOIN:

SELECT Name, Gender, Salary, Dept_Name
FROM 
    tblEmp AS E
    LEFT JOIN
    tblDept AS D
        ON E.Dept_id = D.ID

Result:

Name    Gender  Salary  Dept_Name 
abc     male    2004    IT 
Tom     female  5004    HR 
Sara    female  29404   HR 
Jim     male    8604    Accounts 
Lisan   male    2078    IT 
Brad    male    9804    Accounts 
Diana   female  2095    HR 
Henry   male    28204   HR 
Mark    male    20821   IT 
Miley   female  9456    IT 
Richie  male    8604    NULL 
Lisan   female  20776   NULL

OR

If you need show all employees and all departments, you can use FULL JOIN:

SELECT Name, Gender, Salary, Dept_Name
FROM 
    tblEmp AS E
    FULL JOIN
    tblDept AS D
        ON E.Dept_id = D.ID

Result:

Name    Gender  Salary  Dept_Name
abc     male    2004    IT
Tom     female  5004    HR
Sara    female  29404   HR
Jim     male    8604    Accounts
Lisan   male    2078    IT
Brad    male    9804    Accounts
Diana   female  2095    HR
Henry   male    28204   HR
Mark    male    20821   IT
Miley   female  9456    IT
Richie  male    8604    NULL
Lisan   female  20776   NULL
NULL    NULL    NULL    OtherDepartment

Answer 3

The result is correct, the cross join will give you all combinations based on two tables: tblEmp and tblDept.

And since you use Dept_Name as the combination, without where clause, it will give you every combination possible between your two tables:

Name    Gender  Salary  Dept_Name
abc   male      2004    IT
abc   male      2004    HR
abc   male      2004    Accounts
abc   male      2004    OtherDepartment
Tom   female    5004    IT
Tom   female    5004    HR
Tom   female    5004    Accounts
Tom   female    5004    OtherDepartment

... and so on

Richie  male    8604    IT
Richie  male    8604    HR
Richie  male    8604    Accounts
Richie  male    8604    OtherDepartment
Lisan   female  20776   IT
Lisan   female  20776   HR
Lisan   female  20776   Accounts
Lisan   female  20776   OtherDepartment

That is, by cross-joining, you would actually get 12 (from tblEmp) x 4 (from tblDept) = 48 rows

Then your where clause will simply take away everybody except Richie and Lisan, since the two of them are the only ones having Dept_id = NULL

Name    Gender  Salary  Dept_Name
Richie  male    8604    IT
Richie  male    8604    HR
Richie  male    8604    Accounts
Richie  male    8604    OtherDepartment
Lisan   female  20776   IT
Lisan   female  20776   HR
Lisan   female  20776   Accounts
Lisan   female  20776   OtherDepartment

If you query Dept_id column too,

select Name, Gender, Salary, Dept_id, Dept_Name
from tblEmp 
CROSS JOIN tblDept 
where tblEmp.Dept_id is NULL

The result will be clearer, as you actually only get the employees with Dept_id = NULL:

Name    Gender  Salary  Dept_id Dept_Name
Richie  male    8604    NULL    IT
Richie  male    8604    NULL    HR
Richie  male    8604    NULL    Accounts
Richie  male    8604    NULL    OtherDepartment
Lisan   female  20776   NULL    IT
Lisan   female  20776   NULL    HR
Lisan   female  20776   NULL    Accounts
Lisan   female  20776   NULL    OtherDepartment

Your Dept_Name column comes from 4 tblDept entries, not from tblEmp entries.

Answer 4

A CROSS JOIN would give you each row of the first table join with each row of the second table, (a Cartesian product) unless you add a condition using the where clause to connect the two tables (and in that case, it behaves like an inner join)

Here is a quick demonstration of Cross join:

DECLARE @A table 
(
    A1 int identity(1,1),
    A2 int
)

DECLARE @B table 
(
    B1 int identity(1,1),
    B2 int
)

INSERT INTO @A VALUES (1), (2),  (NULL)

INSERT INTO @B VALUES (4), (5), (6)

SELECT *
FROM @A
CROSS JOIN @B

Results:

A1          A2          B1          B2
----------- ----------- ----------- -----------
1           1           1           4
2           2           1           4
3           NULL        1           4
1           1           2           5
2           2           2           5
3           NULL        2           5
1           1           3           6
2           2           3           6
3           NULL        3           6

As you can see, for each record in table @A, you join each record of table @B

SELECT *
FROM @A
CROSS JOIN @B
WHERE A2 IS NULL

Results:

A1          A2          B1          B2
----------- ----------- ----------- -----------
3           NULL        1           4
3           NULL        2           5
3           NULL        3           6

As you can see, for each record in table @A where A2 is null, you join each record of table @B.