Home › SQL › Comparing tables using LEFT and RIGHT JOINS

Comparing tables using LEFT and RIGHT JOINS

December 19, 2009 - 1:08pm — Xuan Ngo

Comparing tables using LEFT and RIGHT joins is easy. We will exploit the definitions of the LEFT and RIGHT joins to compare every records in every columns of each table. By definition, the LEFT join will returns all records of the left table even though the ON clause doesn't find any matching records on the right table. The non-matching records in the right table is still returned with the record of the left table but its value is NULLed. The RIGHT join is exactly the same as the LEFT join, except that it returns all records of the right table instead of the left table.

Let's have an example to make it clear. Suppose that we have 2 tables, t1 and t2. The records inside both tables are shown below.

SELECT * FROM t1;         SELECT * FROM t2; 
-- +------+------+        -- +------+------+
-- | c1   | c2   |        -- | c1   | c2   |
-- +------+------+        -- +------+------+
-- | A    |   12 |        -- | A    |   12 |
-- | G    |   62 |        -- | B    |   62 |
-- +------+------+        -- +------+------+

For simplicity, we will only do a LEFT join on the records of the 1st colum(c1). Here are the results:
SELECT t1.c1 AS t1_c1, t2.c1 AS t2_c1
FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
-- +-------+-------+
-- | t1_c1 | t2_c1 |
-- +-------+-------+
-- | A     | A     | 
-- | G     | NULL  | 
-- +-------+-------+
The only matching records of the 1st column(c1) on both tables is (A, A). Since, there is no G record in the 1st column of the 2nd table, the join is also returning G record of the left table(t1) and a NULL value for right table(t2). Looking at the results, all records of the left table(t1) are returned. Therefore, all records that don't exist in 1st column(t2_c1) of the 2nd table will always have NULL as value. With this characteristic, we simply add a WHERE condition to return only rows that contains NULL value to get non-matching records between both tables.
SELECT t1.c1 AS t1_c1, t2.c1 AS t2_c1
FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1
WHERE t2.c1 IS NULL;
-- +-------+-------+
-- | t1_c1 | t2_c1 |
-- +-------+-------+
-- | G     | NULL  | 
-- +-------+-------+



Complete SQL script to compare 2 tables

-- DROP TABLE t1;
-- DROP TABLE t2;
 
-- Create tables and insert data.----
-- ----------------------------------
CREATE TABLE t1 (c1 CHAR(1), c2 INT);
  INSERT INTO t1 VALUES( 'A', 12);
  INSERT INTO t1 VALUES( 'G', 62);
 
 
 
CREATE TABLE t2 (c1 CHAR(1), c2 INT);
  INSERT INTO t2 VALUES( 'A', 12);
  INSERT INTO t2 VALUES( 'B', 62);
 
SELECT * FROM t1;
SELECT * FROM t2;
 
 
-- Comparison starts here. ----------
-- ----------------------------------
 
-- LEFT JOIN only
SELECT t1.c1 AS t1_c1, t2.c1 AS t2_c1
FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
 
 
-- LEFT JOIN and filter NULL
SELECT t1.c1 AS t1_c1, t2.c1 AS t2_c1
FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1
WHERE t2.c1 IS NULL;
 
-- RIGHT JOIN only
SELECT t1.c1 AS t1_c1, t2.c1 AS t2_c1
FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1;
 
-- RIGHT JOIN and filter NULL
SELECT t1.c1 AS t1_c1, t2.c1 AS t2_c1
FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c1 IS NULL;