Recipe3.3.Finding Rows in Common Between Two Tables

Recipe 3.3. Finding Rows in Common Between Two Tables

Problem

You want to find common rows between two tables but there are multiple columns on which you can join. For example, consider the following view V:

  create view V as select ename,job,sal   from emp  where job = 'CLERK' select * from V ENAME       JOB              SAL ----------  --------- ---------- SMITH       CLERK            800 ADAMS       CLERK           1100 JAMES       CLERK            950 MILLER      CLERK           1300 

Only clerks are returned from view V. However, the view does not show all possible EMP columns. You want to return the EMPNO, ENAME, JOB, SAL, and DEPTNO of all employees in EMP that match the rows from view V. You want the result set to be the following:

    EMPNO  ENAME       JOB             SAL     DEPTNO --------  ----------  --------- ---------- ---------    7369   SMITH       CLERK           800         20    7876   ADAMS       CLERK          1100         20    7900   JAMES       CLERK           950         30    7934   MILLER      CLERK          1300         10 

Solution

Join the tables on all the columns necessary to return the correct result. Alternatively, use the set operation INTERSECT to avoid performing a join and instead return the intersection (common rows) of the two tables.

MySQL and SQL Server

Join table EMP to view V using multiple join conditions:

 1 select e.empno,e.ename,e.job,e.sal,e.deptno 2   from emp e, V 3  where e.ename = v.ename 4    and e.job   = v.job 5    and e.sal   = v.sal 

Alternatively, you can perform the same join via the JOIN clause:

 1 select e.empno,e.ename,e.job,e.sal,e.deptno 2   from emp e join V 3     on (    e.ename   = v.ename 4        and e.job     = v.job 5        and e.sal     = v.sal ) 

DB2, Oracle, and PostgreSQL

The MySQL and SQL Server solution also works for DB2, Oracle, and PostgreSQL. It's the solution you should use if you need to return values from view V.

If you do not actually need to return columns from view V, you may use the set operation INTERSECT along with an IN predicate:

 1 select empno,ename,job,sal,deptno 2   from emp 3  where (ename,job,sal) in ( 4   select ename,job,sal from emp 5   intersect 6   select ename,job,sal from V 7  ) 

Discussion

When performing joins, you must consider the proper columns to join on in order to return correct results. This is especially important when rows can have common values for some columns while having different values for others.

The set operation INTERSECT will return rows common to both row sources. When using INTERSECT, you are required to compare the same number of items, having the same data type, from two tables. When working with set operations keep in mind that, by default, duplicate rows will not be returned.