How to Query Overlapping SQL Tables

Your data has drifted into two tables (unfortunately), and now you need to merge them in a query…

Photo by Lukas Blazek on Unsplash

In the wilds of data, you may have encountered a situation where two tables contain overlapping information, and you need a performant way to combine both in one result set.

Let’s keep it simple and say you have two tables of user profile information with columns for user_id and bio. Between these two tables, there are instances of overlapping user_ids.

Overlapping Tables

As a business rule, you want to combine the two tables while preserving data from table two when there is an overlap. What is the most concise and elegant way to handle this in a query?

Here are three options that will return the desired results.

Option 1: Union and Left Join

This approach is intuitive and may be the first that comes to mind. Data from the two tables is appended together with a UNION operator, and overlapping rows from the first table are filtered using a left join.

WITH users AS (
  SELECT
    p1.user_id,
    p1.bio…