select 'low' from emp where sal < 4000 and ....

SELECT E.*, 'LOW' 
FROM EMP E
WHERE SAL < 4000
UNION ALL
SELECT E.*, 'HIGH' 
FROM EMP E
WHERE SAL > 4000;

SELECT E.*, 'LOW' as low, null as high
FROM EMP E
WHERE SAL < 4000
.....

SELECT E.*, 'LOW' as LOW, NULL as HIGH
FROM EMP E
WHERE SAL < 4000
UNION ALL
SELECT E.*, NULL as HIGH, 'HIGH' as HIGH
FROM EMP E
WHERE SAL > 4000;

select
e.ename,e.sal,
coalesce(
(select 'low' from emp f where f.empno=e.empno and sal < 4000),
(select 'high' from emp f where f.empno=e.empno and sal >= 4000)
)
from emp e;

Plan hash value: 1301082189

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   560 |     6   (0)| 00:00:01 |
|   1 |  UNION-ALL         |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     8 |   320 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     6 |   240 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SAL"<4000)
   3 - filter("SAL">4000)

orclz>

Execution Plan
----------------------------------------------------------
Plan hash value: 732000419

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    15 |   225 |    18   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID | EMP    |     1 |     9 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN          | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |     9 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
|   5 |  TABLE ACCESS FULL           | EMP    |    15 |   225 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SAL"<4000)
   2 - access("F"."EMPNO"=:B1)
   3 - filter("SAL">=4000)
   4 - access("F"."EMPNO"=:B1)

orclz>

SQL> explain plan for
  2  with t as (
  3             select  4000 sal,
  4                     'HIGH' grade
  5               from  dual
  6            )
  7  select  e.*,
  8          nvl(grade,'LOW') grade
  9    from  emp e,
 10          t
 11    where t.sal(+) <= e.sal
 12  /

Explained.

SQL> select  *
  2    from  table(dbms_xplan.display)
  3  /

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 4010907715

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   560 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN OUTER   |      |    14 |   560 |     6  (17)| 00:00:01 |
|   2 |   SORT JOIN         |      |    14 |   532 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |      |     1 |     2 |     2   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access(INTERNAL_FUNCTION("E"."SAL")>=CASE  WHEN (ROWID(+) IS NOT
              NULL) THEN 4000 ELSE NULL END )
       filter(INTERNAL_FUNCTION("E"."SAL")>=CASE  WHEN (ROWID(+) IS NOT
              NULL) THEN 4000 ELSE NULL END )

20 rows selected.

SQL> 

Select e.*, 
       Nvl(t.grade,'High') grade
  From emp e
       Left Join 
       (Select -1 grp, 'Low' grade from dual) t
    On t.grp = sign(sal - 4000)

SQL> declare
  2      v_sum number := 0;
  3      cursor v_cur
  4        is
  5  with t as (
  6             select  4000 sal,
  7                     'HIGH' grade
  8               from  dual
  9            )
 10  select  e.*,
 11          nvl(grade,'LOW') grade
 12    from  emp e,
 13          t
 14    where t.sal(+) <= e.sal;
 15  begin
 16      for v_i in 1..100000 loop
 17        for v_rec in v_cur loop
 18          v_sum := v_sum + v_rec.sal;
 19        end loop;
 20      end loop;
 21  end;
 22  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.18
SQL> declare
  2      v_sum number := 0;
  3      cursor v_cur
  4        is
  5  Select e.*, 
  6         Nvl(t.grade,'High') grade
  7    From emp e
  8         Left Join 
  9         (Select -1 grp, 'Low' grade from dual) t
 10      On t.grp = sign(sal - 4000);
 11  begin
 12      for v_i in 1..100000 loop
 13        for v_rec in v_cur loop
 14          v_sum := v_sum + v_rec.sal;
 15        end loop;
 16      end loop;
 17  end;
 18  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.48
SQL> 

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 

SQL> set timi on
SQL> 
SQL> declare
  2     v_sum number := 0;
  3     cursor v_cur
  4     is
  5     with t as (
  6          select  4000 sal,
  7                  'HIGH' grade
  8            from  dual
  9     )
 10    select  e.*,
 11            nvl(grade,'LOW') grade
 12      from  emp e,
 13            t
 14      where t.sal(+) <= e.sal;
 15  begin
 16    for v_i in 1..100000 loop
 17       for v_rec in v_cur loop
 18           v_sum := v_sum + v_rec.sal;
 19        end loop;
 20    end loop;
 21  end;
 22  /
PL/SQL procedure successfully completed
Executed in 16,443 seconds
SQL> declare
  2     v_sum number := 0;
  3     cursor v_cur
  4     is
  5     Select e.*,
  6            Nvl((Select 'Low' grade from dual where Sign(sal-4000) = -1),'High') grade
  7       From emp e;
  8  begin
  9    for v_i in 1..100000 loop
 10       for v_rec in v_cur loop
 11           v_sum := v_sum + v_rec.sal;
 12        end loop;
 13    end loop;
 14  end;
 15  /
PL/SQL procedure successfully completed
Executed in 13,54 seconds

SQL>