NOT Operator

The not command is used to exclude rows from a table that match a specified condition.

Example:

select * from employees where job_id = 5 and not salary < 9000;

The above query will fetch employes whose job_id is 5, but the salary is not less than 9000.

Result:

In the example, only 2 employees have job_id 5 and a salary greater than or equal to 9000.

select * from employees where not salary > 9000 ;

instead of writing it as above, you could write it as,

select * from employees where salary < 9000 ;

Ecto query for not

not/1

Expression

In Ecto, not/1 is used to exclude values inside where/3.

Example:

HR.Employee
|> where([c], c.job_id == 5 and not(c.salary < 9000))
|> HR.Repo.all()

In this example:

  • [c] is the reference variable that is used to refer to HR.Employee. To know about reference variable, refer to Aliases in Ecto.
  • We are filtering for records where job_id is 5 and the salary is not less than 9000.

Result:

IEx(16)> HR.Employee |> where([c], c.job_id == 5 and not(c.salary < 9000) ) |> HR.Repo.all()

[
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 101,
    first_name: "Neena",
    last_name: "Kochhar",
    email: "neena.kochhar@sqltutorial.org",
    phone_number: "515.123.4568",
    hire_date: ~D[1989-09-21],
    salary: Decimal.new("17000.00"),
    manager_id: 100,
    job_id: 5,
    department_id: 9
  },
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 102,
    first_name: "Lex",
    last_name: "De Haan",
    email: "lex.de haan@sqltutorial.org",
    phone_number: "515.123.4569",
    hire_date: ~D[1993-01-13],
    salary: Decimal.new("17000.00"),
    manager_id: 100,
    job_id: 5,
    department_id: 9
  }
]

Keywords

HR.Repo.all(from c in HR.Employee, where: c.job_id == 5 and not(c.salary < 9000))
Back to Homepage