使用OR：

SELECT *
FROM   emp
WHERE  firstname IN ('JOHN', 'SCOTT', 'MARK', 'Ann')
OR     lastname  IN ('JOHN', 'SCOTT', 'MARK', 'Ann')

可以将JOIN作为包含所有名称的子查询

CREATE TABLE emp (firstname varchar2(100),lastname varchar2(100))

INSERT INTO emp VALUES ('JOHN', 'test')

select * from emp INNER JOIN
  (SELECT 'JOHN' name FROM DUAL
  UNION ALL
  SELECT 'SCOTT' FROM DUAL
    UNION ALL
  SELECT 'MARK' FROM DUAL
    UNION ALL
  SELECT 'Ann' FROM DUAL) t1 ON name = firstname OR name = lastname

| FirstName| LastName|名称|
| --|--|--|
| 约翰|测试|约翰|
fiddle

还有一种选择是指定在集合中搜索的所有值（内置的sys.odcivarchar2list就可以;你不必创建自己的），用table函数提取它们（结果是column_value），并将结果连接到原始表。
样本数据：

SQL> with emp (id, firstname, lastname) as
  2    (select 1, 'John', 'King'  from dual union all
  3     select 2, 'Foot', 'Mike'  from dual union all
  4     select 3, 'Ann' , 'Tiger' from dual union all
  5     select 4, 'Mark', 'Scott' from dual
  6    )

查询方式：

7  select e.*
  8  from emp e join table(sys.odcivarchar2list('John', 'Ann', 'Mark')) t on
  9               t.column_value in (e.firstname, e.lastname);

        ID FIRSTNAME       LASTNAME
---------- --------------- ---------------
         1 John            King
         3 Ann             Tiger
         4 Mark            Scott

SQL>