Is Null Equal To Null
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
Is null equal to null?
No. null is not equal to null.
null is not nothing.
It is unknown.
An unknown value cannot be another unknown value.
Try this SQL code.
select "Codepedia" where null = null
Will that return any row. No.
Since both the values are unknown the condition cannot be true.
Try this.
select "Codepedia" where isnull(null,0) = isnull(null,0)
Will this return any row.
Yes!!!. It will return a row "Codepedia".
Why? Because we have specified in the code to replace both null values with 0(zero).
Since null is replaced with zero on both instances, 0 equals 0.
The condition is true and a row is returned.
Try this.
select "Codepedia" where isnull(null,1) = isnull(null,0)
Will this return a row.
No. It wont. Because the code will replace the first null with 1 and the second null with 0.
1 is not equal to 0.
The condition fails and no row is returned.
SQL Joins
Consider two tables employee and salary.
Employee
Salary
Inner Join
select ep.name, sy.amount
from employee ep
inner join salary sy on sy.employee_id = ep.employee_id
The above code will return rows with employee_id in both the tables.
Left Join
select ep.name, sy.amount
from employee ep
left join salary sy on sy.employee_id = ep.employee_id
The above code will return all the rows from the employee table.
If no matching employee_id from the employee table is found in the salary table, it returns a null value.
Right Join
select ep.name, sy.amount
from employee ep
right join salary sy on sy.employee_id = ep.employee_id
The above code will return all the rows from the salary table.
If no matching employee_id from the salary table is found in the employee table a null value is returned.
NOTE
select ep.name, sy.amount
from employee ep
left join salary sy on sy.employee_id = ep.employee_id
is the same as
select ep.name, sy.amount
from salary sy
right join employee ep on sy.employee_id = ep.employee_id
Employee
| Employee_id | Name | Age |
| 1 | George | 56 |
| 2 | Tom | 23 |
| 3 | Lee | 35 |
| 4 | Jade | 49 |
Salary
| Salary_id | Employee_id | Amount |
| 1 | 4 | 45000 |
| 2 | 5 | 50500 |
| 3 | 1 | 17500 |
| 4 | 2 | 24000 |
| 5 | 6 | 17000 |
Inner Join
select ep.name, sy.amount
from employee ep
inner join salary sy on sy.employee_id = ep.employee_id
The above code will return rows with employee_id in both the tables.
| Name | Amount
|
| Jade | 45000 |
| George | 17500 |
| Tom | 24000 |
Left Join
select ep.name, sy.amount
from employee ep
left join salary sy on sy.employee_id = ep.employee_id
The above code will return all the rows from the employee table.
If no matching employee_id from the employee table is found in the salary table, it returns a null value.
| Name | Amount |
| George | 17500 |
| Tom | 24000 |
| Lee | NULL |
| Jade | 45000 |
Right Join
select ep.name, sy.amount
from employee ep
right join salary sy on sy.employee_id = ep.employee_id
The above code will return all the rows from the salary table.
If no matching employee_id from the salary table is found in the employee table a null value is returned.
| Name | Amount |
| Jade | 45000 |
| NULL | 50500 |
| George | 17500 |
| Tom | 24000 |
| NULL | 17000 |
NOTE
select ep.name, sy.amount
from employee ep
left join salary sy on sy.employee_id = ep.employee_id
is the same as
select ep.name, sy.amount
from salary sy
right join employee ep on sy.employee_id = ep.employee_id
Subscribe to:
Posts (Atom)