AND Operator

The and operator is used to check for multiple conditions in a query. It will return records that satisfy all the specified conditions.

For Example:

Let's apply the and condition for the employees table.

select * from employees where phone_number is nill and first_name ILIKE 'J%';

Conditions.

  • phone_number is null
  • first_name ILIKE 'J%'

We are using the and operator in between the two conditions. So it will look for employees whose phone_number is nul and first_name starts from J or j. The and will return only records that satisfy both the conditions.

Result:

You can use as many and statements as needed.

Ecto query for and

and/2

Expression example

In Ecto.Query You can check for two condition with the and/2 function

For Example:

HR.Employee
|> where([c], is_nil(c.phone_number) and ilike(c.first_name, "J%"))
|> HR.Repo.all()

Conditions

  • is_nil(c.phone_number)
  • ilike(c.first_name, "J%")

The above query will return employees whose phone_number is null and first_name starts with J or j. The c in both conditions are called reference variable which is referring to the struct HR.Employee. To know about reference variable refer reference variable.

Result:

IEx(14)> HR.Employee |> where([c], is_nil(c.phone_number) and ilike(c.first_name, "J%")) |> 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: 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
  }
]

Keyword example

HR.Repo.all(from c in HR.Employee, where: is_nil(c.phone_number) and ilike(c.first_name, "J%"))
Back to Homepage