How to Join Tables in SQL Without Using JOINs

Here’s how you can combine tables without the JOIN keyword.

It seems like it shouldn’t be possible: join tables in SQL without using the JOIN keyword. But using the JOIN keyword is not the only way to join tables in SQL.

This article will show you two additional methods for joining tables. Neither of them requires the JOIN keyword to work. They are:

1. Using a comma between the table names in the FROM clause and specifying the joining condition in a WHERE
2. Using UNION/UNION ALL.

If you need a refresher, I recommend our interactive SQL JOINs course. It is the best way to review and practice SQL JOINs. It contains over 90 hands-on exercises that cover simple JOINs, joining multiple tables, LEFT JOIN, RIGHT JOIN, FULL JOIN, and many more. The examples are based on real-world scenarios, you practice on problems you'll meet daily.

The First Method: FROM and WHERE

Syntax

The syntax for this method of joining tables without using JOINs is:

SELECT *
FROM , 
WHERE 

You can replace the JOIN keyword with a comma in the FROM clause. What do you do next? There’s no ON keyword for you to state the joining condition as there would be when using JOIN, e.g., on which two columns you want to join the tables. In this method, you simply use a WHERE clause to do so.

This method does exactly the same as:

SELECT *
FROM <table1> JOIN <table2>
ON <condition>

Let’s see how it works in practice.

Example

In this example, I need to find the artist’s name, the albums he/she has recorded, and the years in which they were recorded, for every artist.

To show you how this method works, I’ll use two tables. The first one is artist:

It’s a simple table containing several of my favorite musicians.

The other table, album, looks like this:

These are the albums recorded by the musicians from the first table.

Query

The query for joining these tables without a JOIN keyword looks like this:

SELECT
    artist_name,
    album_name,
    year_recorded
FROM artist, album
WHERE artist.id = album.artist_id;

First, I specify the columns I want to see in my result: artist_name, album_name, and year_recorded. They come from either artist or album. I join these two tables simply by listing them in the FROM clause and separating them with a comma.

The WHERE clause is generally used to filter results. I can use it to filter this result so that it shows all data where the column id from the table artist is equal to the column artist_id from the table album. You can see the condition in WHERE in this query is the same as it would be after the ON keyword when using JOIN: artist.id = album.artist_id.

The equivalent query with a JOIN keyword is:

SELECT 	artist_name,
		album_name,
		year_recorded
FROM artist
JOIN album
ON artist.id = album.artist_id;

Result

Both of these queries return the same result:

You see the result combines data from the two initial tables. I can now see, for example, Paul Simon recorded the album “In the Blue Light” in 2018. If you don’t trust my query, you can always check Wikipedia!

This method uses a common column to join tables. Recall that you can also join tables that do not have a common column.

The Second Method: UNION/UNION ALL

Syntax

SELECT
    ,
    ,
    …
FROM 

UNION / UNION ALL

SELECT
    ,
    ,
    …
FROM 

Both UNION and UNION ALL are used to glue together the result of the first query with that of the second query. You simply write two SELECT statements and write UNION or UNION ALL between them. But what is the difference between those two SQL keywords? Recall:

• UNION removes duplicate rows from the query and shows them only once.
• UNION ALL shows all the rows from both tables, no matter how many duplicates there are.

For both UNION and UNION ALL to work, all columns in the two SELECT statements need to match. This means the number of columns selected must be the same, and the data types of those columns must be the same.

These two operators are called set operators. Read this article to find out more about these and other set operators.

Example

We have two tables in this example, also. The first is named customer:

It contains a list of my customers.

The second table is supplier:

If you inspect these tables a little closer, you see some people are in both tables. For example, look at Steve Inicks – this means Steve is, along with some others, both my customer (he buys some services from my company) and my supplier (he provides some services to me).

Now, let’s see what it looks like when I join these tables with UNION or UNION ALL.

Query: UNION

This method of combining tables without using the JOIN keyword translates to the following query:

SELECT 	
    first_name,
    last_name
FROM customer

UNION

SELECT	
    first_name,
    last_name
FROM supplier;

The first SELECT statement selects the columns first_name and last_name from the table customer. The second SELECT statement does the same but from the table supplier. I use UNION to join the results of these two queries. It works: there are two columns in both SELECT statements, and they are of the same data type. They are even named the same, although that is not necessary for the query to work.

Result: UNION

Running the query gets you this:

As I mentioned, it’s a combination of all data from both tables.

Let’s check for duplicates. I’ve mentioned Steve Inicks is in both customer and supplier tables. In the query result, you find him only once. I’ve marked the row where he is.

Let’s see how this is different from UNION ALL.

Query: UNION ALL

The query in this case is:

SELECT
    first_name,
    last_name
FROM customer

UNION ALL

SELECT
    first_name,
    last_name
FROM supplier;

As you can see, this isn’t very different. In fact, it is the same query, except it has UNION ALL instead of UNION.

However, the results are different. Let’s take a look.

Result: UNION ALL

The result of the query is:

Knowing Steve Inicks appears as both a customer and a supplier, let’s check how many times he appears in the result. He is there twice, both marked in blue. I’ve also marked three others who appear twice: Alpa Cino, Bert Randrussell, and Carmen Naburana.

Here’s another example of using UNION and UNION ALL..

Yes, Tables Can Be Joined Without the JOIN Keyword

As you have just seen, it’s not always necessary to use the JOIN keyword to combine two tables in SQL.

You can replace it with a comma in the FROM clause then state your joining condition in the WHERE clause.

The other method is to write two SELECT statements. The result of each is a table, so simply use UNION or UNION ALL to combine the two. Keep in mind this works only if your SELECT statements have the same number of columns and the same data types.

Use UNION when you don’t want to show duplicates and UNION ALL when you want to see duplicates.

Learn all the Possible Ways for Joining Tables in SQL

The more ways you know how to join tables in SQL, the better. With these two methods plus the JOIN, joining tables becomes easier. Your query results are closer to what you need, and you get more out of SQL.

Knowing a variety of ways to join tables also allows you to shake up your code a little. It can be boring to write everything the same way all the time. But most importantly, each of the three methods is useful in different situations.

You can learn all these methods of joining tables in our SQL Basics course. This is only a first step toward mastering SQL from A to Z. You also need a lot of practice to master joining tables. Maybe, the best way is to choose at least one online option from our list of ten.