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
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.