How to find rows common between two tables?

Staff Table

empno ename job mgr_no hiredate sal commission deptno
7369 ALI CLERK 7902 1980-12-17 800.00 NULL 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 NULL 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 NULL 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 NULL 10
7788 RAHUL ANALYST 7566 1982-12-09 3000.00 NULL 20
7839 RAJ PRESIDENT NULL 1981-11-17 5000.00 NULL 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAM CLERK 7788 1983-01-12 1100.00 NULL 20
7900 JAMES CLERK 7698 1981-12-03 950.00 NULL 30
7902 ARHAM ANALYST 7566 1981-12-03 3000.00 NULL 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 NULL 10

Department Table

deptno dname loc
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

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 created from the Staff table for teaching purposes:

CREATE view V
as
SELECT ename,job,sal 
FROM Staff
WHERE job = 'CLERK'
SELECT * FROM V

Output

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 Staff columns. You want to return the empno, ename, job, sal, and deptno of all employees in Staff that match the rows from view V.

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 Staff to view V using multiple join conditions

SELECT s.empno,s.ename,s.job,s.sal,s.deptno
FROM Staff s, V
WHERE s.ename = v.ename
AND s.job = v.job
AND s.sal = v.sal

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

SELECT s.empno,s.ename,s.job,s.sal,s.deptno
from Staff s join V
ON ( s.ename = v.ename
AND s.job = v.job
AND s.sal = v.sal )

Output

empno ename job sal deptno
7369 ALI CLERK 800 20
7876 ADAMS CLERK 1100 20
7900 JAMES CLERK 950 30
7934 MILLER CLERK 1300 10

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:

SELECT empno,ename,job,sal,deptno
FROM Staff
WHERE (ename,job,sal) in (
SELECT ename,job,sal FROM Staff

INTERSECT 

SELECT ename,job,sal FROM V )

Output

empno ename job sal deptno
7369 ALI CLERK 800 20
7876 ADAMS CLERK 1100 20
7900 JAMES CLERK 950 30
7934 MILLER CLERK 1300 10

Discussion

When performing joins, you must consider the proper columns to join 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.