Forum Stats

  • 3,826,326 Users
  • 2,260,624 Discussions
  • 7,896,898 Comments

Discussions

How to avoid using UNION?

Rob8111
Rob8111 Member Posts: 24 Green Ribbon

Hi, I have a table Employees with

employee_name

Age

In apex page I have a page item(p123_page_selector), which is a radio button. Its values are ALL and AGED.

So, when user selects 'ALL', I want to return all data from table employees.

When user selects 'AGED', I want to return all data from employees where age greater than 35.

I wrote query like below.

How to write this query without using 'UNION'?

Best Answers

  • cormaco
    cormaco Member Posts: 1,942 Silver Crown
    edited May 7, 2022 8:00AM Answer ✓

    Like this (untested):

    select *
    from employees
    where
      :p123_page_selector = 'ALL'
      or
      (age > 35 and :p123_page_selector = 'AGED')
    

    Two comments on your select:

    UNION forces the removal of duplicates which is not necessary here, UNION ALL is sufficient.

    You should not store the age of a person in the database, store the birthdate instead.

    Rob8111mathguy
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,052 Red Diamond
    Answer ✓

    Hi, @Rob8111

    In apex page I have a page item(p123_page_selector), which is a radio button. Its values are ALL and AGED.

    If :p123_page_selector will always be either 'ALL' or 'AGED', then you can simplify Cormaco's solution like this:

    select *
    from employees
    where
      :p123_page_selector = 'ALL'
      or
      age > 35
    


Answers