OR Operator

The or command is used to check if either one of the conditions in where is true. It is commonly used in the where clause.

For Example:

Now let's apply this or condition to the employees table.

select * from employees where phone_number is null or first_name ILIKE 'A%';

Conditions

  • phone_number is null
  • first_name ILIKE 'A%'

It works the same as and, but or checks if either one of the conditions is true. Result:

The first 3 records satisfy the first_name ILIKE 'A%' condition while the next 6 rows satisfy the phone_number is null condition. Each record satisfies either of the conditions.

Ecto query for or

or/2

Expression example

In Ecto.Query you can do it with the use of or/2 function.

Example:

HR.Employee
|> where([c], is_nil(c.phone_number) or ilike(c.email,"bruce.ernst@sqltutorial.org"))
|> HR.Repo.all()

Conditions:

  • is_nil(c.phone_number)
  • ilike(c.email, "bruce.ernst@sqltutorial.org")

The c in both conditions is a reference variable referring to the struct HR.Employee. To learn about the reference variable refer to reference variable.

Result:

IEx(15)> HR.Employee |> where([c], is_nil(c.phone_number) or ilike(c.email, "bruce.ernst@sqltutorial.org")) |> HR.Repo.all()

[
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 104,
    first_name: "Bruce",
    last_name: "Ernst",
    email: "bruce.ernst@sqltutorial.org",
    phone_number: "590.423.4568",
    hire_date: ~D[1991-05-21],
    salary: Decimal.new("6000.00"),
    manager_id: 103,
    job_id: 9,
    department_id: 6
  },
  %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
  }

  <!-- and so on -->

]
IEx(16)>

In ths example, The first struct's email is bruce.ernst@sqltutorial.org, satisfying the ilike(c.email, "bruce.ernst@sqltutorial.org") condition. All other structs have phone_number as nil which is satisfying the is_nil(c.phone_number) condition.

Keyword example

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