Tips About NULL in SQL

Posted on Edited on In

Select

should use is null, not = null because NULL is a special marker to indicate a data value does not exist, not a data value. So that cannot use = to check for equality.

Output NULL

To output null as a value, should use another select clause as a shell, for example:

1
2
3
4
5
6
select (
	select distinct salary as SecondHighestSalary
	from Employee
	order by salary desc
	limit 1 offset 1
) as SecondHighestSalary

The clause will output the second higher salary but cannot output “NULL” if the database is null. The shell clause will transfer them to the null.

Additional, to assign a variable, using = is Ok.