SQL: Joining Table

Stephen Ayeni

7 min read

·

Jun 23, 2026

--

Listen

Share

Source: Joining Data in SQL

Press enter or click to view image in full size

We have already built a solid foundation in SQL with querying, filtering, grouping and transforming. Now, we will tackle the next level: combing data from multiple tables.

Why is this important: Anytime we want to analyse meaningful, the data we need is rarely ever stored on the same data table

In our use case of being an NFL Draft Analyst, you will rarely ever find data on receivers in the SEC, B1G Ten, Big 12, ACC all stored in one data table.

As a government analyst, you will rarely find data on Human Development like Health, Education, Employment all in one data table.

So to analyse problem and answer data questions, we need to pull data from various sources (data sets, data tables etc.)

They are two direction to which we must join data:

1. Vertical Combining: We stack rows from different tables on the same column structure, So like SEC QB list for each year from 2014–2025 or GDP for each region in the United Kingdom -> Midland Statistics for each city, NorthWest Stats for each city (they must have the same columns in the table)
2. Horizontal Combining: Pull data which have a shared key (or factor) together so we have a more comprehensive view of the data in focus. We pull related data together
   For example, As an NFL Draft Analyst — combining data on their Medicals (Height, Weight, Bench Press Max etc.) and Game Stats (Yards, Touchdowns, Yards after contact) and Academics (Programs, Grades, Attendance)
   Or for cities: Collecting data on their
   Economy: GDP, Export, Import, Investment, Government Spending etc.
   Tax: Taxable income, Tax Population, Average Income of Resident
   Government Spending: Total Expenditure, Total Debt

At the end of this course, we want to know which style fits each situation best, how to set it up correctly, implement on SQL and verify results.

This is the workhorse of day-to-day data work.

Concisely, we should be able to do the following:

1. Recognise when combining the data from multiple in a necessary way to answer the data question
2. Understand how different approaches -> Unions, Left Joins, Inner Joins etc. — solve different combining needs
3. Design joins by using the right type and the right columns for the intended results
4. Independent data joins on SQL with the right syntax
5. Verify that a JOIN produced the results you wanted

You will quickly notice that data is often split into various data tables that show the same structure. This is exactly the situation where stacking comes in

Why does this happen though:

1. A dataset can be too large to export as a signle file so it is broken into chunks (smaller sets) and then exported. This makes it easier for loading and transfer
2. Data is often divided by time period — separate tables for days, weeks, quarters, months, years, decades etc.
3. Data can be split by region, geography or category and each is maintained separately (and independently)

In all the aforementioned cases, each table has the same structure (columns) but different rows.

Stacking is the technique for bringing all of these tables together into a single table.

In this note, the case study will be podcast streaming

Here the situation, we work at a platform that has two separate categories:

1. Original Catalogue: Ad-supported
2. Premium Catalouge: Subscription-Only

Each of the catalogue is maintained by a different team so they live in different tables.

Our task: We need to produce a list of shows that run for 60 minutes or longer. These show are what we need for long-form ads sponsorship

Remember: A good habit is to always start by what envisioning what you want to see

Press enter or click to view image in full size

Our goal here is to produce a table were each rows shows: podcast episode, run time, category etc.

Specifically, for this task, we want each row to show episode row and run-time (and sorted by duration — longest show first)

The columsn we need are: podcast_id , podcast_name , category and duration_min

Let’s check out the input data

Press enter or click to view image in full size

How do we combine both categories together? Also, notice that the duplicate is highlighted.

SQL has a simple, straight-forward way of addressing and it is called stacking (or appending) or UNION

Press enter or click to view image in full size

We can see that Stacking places the row in on a table undernearth the rows of another table — this must align them by ensuring the column names matches.

This single table would contain all the rows from both sources. And yes, it will include the duplicate — row in original catalogue and premium catalogue — one from each source table.

The SQL operator for this is: UNION ALL — it stacks the rows from two table on top of each other

Let’s check our data again

Press enter or click to view image in full size

Press enter or click to view image in full size

Note: The different table names are highlighted in blue

This is our SQL Code

SELECT *
FROM podcast_originals
UNION ALL
SELECT *
FROM podcast_premium
ORDER BY duration_min DESC;

Press enter or click to view image in full size

We can see a single table that includes the rows of both tables.

Note: UNION ALL is the right choice when we are certain, they will be no duplicates (e.g. Transaction, Game to Game stats for players etc.)

So in cases where they could be duplicates — what do we do?

Now to get only unique rows in our new and combined table, we use the operator UNION DISTINCT instead of UNION ALL

Same structure, same ordering — just no duplicates

SELECT *
FROM podcast_originals
UNION DISTINCT
SELECT *
FROM podcast_premium
ORDER BY duration_min DESC;

Press enter or click to view image in full size

We can see 2 thigns in our screenshot:

1. No Duplicates at the top: Only one row of P0469
2. We have 536 rpws compard to 548 rows which means that 12 rows were identical and have now been eliminated

Recap:

1. UNION ALL shows “how many rows are shown when we combine each row from both tables
2. UNION DISTINCT show “how many distinct rows” when we combine each distinct row from both tables
3. UNION DISTINCT takes more processing time and power than UNION ALL because of the extra step of removing duplicates

One major note pertaining to SQL Servers:
For most SQL servers — PostgreSQL, MySQL, SQL Server writing UNION on its own implies deduplication.
In BigQuery — you must be explicity = UNOIN ALL or UNION DISTINCT and UNION alone does not work

Now, let’s write the code to answer the business question —

Identify each episode that had a runtime of 60 minutes of more

SELECT *
FROM podcasts_originals
UNION DISTINCT
SELECT *
FROM podcasts_premium
WHERE duration_min >= 60
ORDER BY duration_min;

Press enter or click to view image in full size

This works bu the ideal way of writing this is to add a WHERE operator to each table used in the JOIN

Why?

WHERE operator here only applies to filter to the table afrer the UNION DISTINCT so the table: podcasts_premium

So the SQL queryu above is actually incorrect — the right query is:

SELECT *
FROM podcasts_originals
WHERE duration_mins >= 60
UNION DISTINCT
SELECT *
FROM podcasts_premium
WHERE duration_mins >= 60
ORDER BY duration_mins DESC;

So the right approach is to add a WHERE operator to each SELECT block

There is a last rule to round out stacking: Column Compartability

TO use UNION ALL and UNION DISTINCT , all SELECT statement must

1. Have the same number of columns
2. Have compatible data types in the same order

If either condition is not met, so for example, the number of columns don’t match:

1. Table A has 4 columns and Table B has 5 columns
2. OR Table A rank 1 column has a text column and Table B rank 1 has an integer column

SQL will throw an error

One subtle detail to notice — the column name don’t need to match so SQL will take the column name from the first SELECT statement.

Thank you 🙏