IS NULL Operator
The is null command is used to fetch records with no value or null value. It is used in the where clause.
Example:
select * from employees where phone_number is null;
The above code will fetch employees whose phone_number is null or has no value inserted.
Result:
In the above example, you can see that for all 6 rows, there is no value inserted in the phone_number column.
Ecto query for is null
is_nil/1
Expression
In Ecto.Query, you can achieve this using the is_nil/1 function.
Example:
HR.Employee
|> where([c], is_nil(c.phone_number))
|> HR.Repo.all()
The above code searches in the HR.Employee struct where phone_number is set to nil.
where([c], is_nil(c.phone_number)) -> Here [c] is the reference variable that is used to refer to HR.Employee. Refer Aliases in Ecto to know about aliases/reference variables.
IEx(11)> HR.Employee |> where([c], is_nil(c.phone_number)) |> HR.Repo.all()
[
%HR.Employee{
__meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
employee_id: 145,
first_name: "John",
last_name: "Russell",
email: "john.russell@sqltutorial.org",
phone_number: nil,
hire_date: ~D[1996-10-01],
salary: Decimal.new("14000.00"),
manager_id: 100,
job_id: 15,
department_id: 8
},
%HR.Employee{
__meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
employee_id: 146,
first_name: "Karen",
last_name: "Partners",
email: "karen.partners@sqltutorial.org",
phone_number: nil,
hire_date: ~D[1997-01-05],
salary: Decimal.new("13500.00"),
manager_id: 100,
job_id: 15,
department_id: 8
},
%HR.Employee{
__meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
employee_id: 176,
first_name: "Jonathon",
last_name: "Taylor",
email: "jonathon.taylor@sqltutorial.org",
phone_number: nil,
hire_date: ~D[1998-03-24],
salary: Decimal.new("8600.00"),
manager_id: 100,
job_id: 16,
department_id: 8
},
%HR.Employee{
__meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
employee_id: 177,
first_name: "Jack",
last_name: "Livingston",
email: "jack.livingston@sqltutorial.org",
phone_number: nil,
hire_date: ~D[1998-04-23],
salary: Decimal.new("8400.00"),
manager_id: 100,
job_id: 16,
department_id: 8
},
%HR.Employee{
__meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
employee_id: 178,
first_name: "Kimberely",
last_name: "Grant",
email: "kimberely.grant@sqltutorial.org",
phone_number: nil,
hire_date: ~D[1999-05-24],
salary: Decimal.new("7000.00"),
manager_id: 100,
job_id: 16,
department_id: 8
},
%HR.Employee{
__meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
employee_id: 179,
first_name: "Charles",
last_name: "Johnson",
email: "charles.johnson@sqltutorial.org",
phone_number: nil,
hire_date: ~D[2000-01-04],
salary: Decimal.new("6200.00"),
manager_id: 100,
job_id: 16,
department_id: 8
}
<!-- and so on -->
]
Keywords
HR.Repo.all(from c in HR.Employee, where: is_nil(c.phone_number))
where is a key and it accepts a function as a value called is_nil/1. It accepts records from HR.Employee whose phone_number is nil.