Joining Tables or Database Records › Joining Tables › Comparing a column to more than one value

Comparing a column to more than one value

Why you compare more than one value

You may want to compare values in one table to a list of values in another table. For example, you might want to obtain information about all employees whose ID matches the manager IDs in table DEPT. If you know the manager IDs in table DEPT, you could code a SELECT statement that compares employee IDs to a list of manager IDs:

select empid, lastname
  from emp
  where empid in
    (0013, 0011, 0003, 0004, 0007, 0015, 0349, 0321, 0030)

However, this type of query isn't practical in some cases, especially for large tables. CA OLQ provides a method to retrieve this information.

Coding it as a join operation

You can obtain the same information by joining the EMP and DEPT tables where the manager ID in table DEPT is the same as the employee ID in table EMP:

select emp.lastname
  from dept, emp
  where emp.empid = dept.mgrid