Back to articles list Articles Cookbook
8 minutes read

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 and 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:

idartist_name
1Paul Simon
2Marvin Gaye
3Bettye LaVette
4Joni Mitchell
5Sly and the Family Stone

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

The other table, album, looks like this:

idalbum_nameyear_recordedartist_id
1Dance to the Music19685
2Small Talk19745
3Stranger to Stranger20161
4I've Got My Own Hell to Raise20053
5Blues19714
6Court and Spark19744
7A Tribute to the Great Nat "King" Cole19652
8Graceland19861
9Still Crazy After All These Years19751
10In the Blue Light20181
11There's a Riot Goin' On19715
12The Scene of the Crime20073
13Mingus19794
14What's Going On19712
15Here, My Dear19782

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:

artist_namealbum_nameyear_recorded
Paul SimonIn the Blue Light2018
Paul SimonStill Crazy After All These Years1975
Paul SimonGraceland1986
Paul SimonStranger to Stranger2016
Marvin GayeHere, My Dear1978
Marvin GayeWhat's Going On1971
Marvin GayeA Tribute to the Great Nat "King" Cole1965
Bettye LaVetteThe Scene of the Crime2007
Bettye LaVetteI've Got My Own Hell to Raise2005
Joni MitchellMingus1979
Joni MitchellCourt and Spark1974
Joni MitchellBlues1971
Sly and the Family StoneThere's a Riot Goin' On1971
Sly and the Family StoneSmall Talk1974
Sly and the Family StoneDance to the Music1968

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:

idfirst_namelast_name
1JimmyHellas
2FrancoisJambony
3RenataTraviata
4CarmenNaburana
5SteveInicks
6ElviraSamson
7AlpaCino
8RosaSparks
9BernardineSane
10BertRandrussell

It contains a list of my customers.

The second table is supplier:

idfirst_namelast_name
1ArpadPolanski
2BillyVan Persie
3SteveInicks
4Jean-ClaudeZaandam
5EminaArkayeva
6BoMilosz
7JanWrangler
8HeinrichStroopwafel
9HerringoStarr
10JosephineMatijevic
11AlpaCino
12FlintWestwood
13DesPaulmond
14WilmaJackson
15KatrinVerson
16BertRandrussell
17CarmenNaburana
18JimmyTulp
19LuisWolf
20MirandaFanucci

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:

first_namelast_name
AlpaArpad
CinoBilly
JosephineMatijevic
LuisWolf
Jean-ClaudeZaandam
SteveInicks
BillyVan Persie
EminaArkayeva
MirandaFanucci
BoMilosz
JimmyHellas
FrancoisJambony
RosaSparks
KatrinVerson
JanWrangler
BernardineSane
HeinrichStroopwafel
ElviraSamson
RenataTraviata
JimmyTulp
WilmaJackson
ArpadPolanski
BertRandrussell
DesPaulmond
FlintWestwood
CarmenNaburana
HerringoStarr

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:

first_namelast_name
JimmyHellas
FrancoisJambony
RenataTraviata
CarmenNaburana
SteveInicks
ElviraSamson
AlpaCino
RosaSparks
BernardineSane
BertRandrussell
ArpadPolanski
BillyVan Persie
SteveInicks
Jean-ClaudeZaandam
EminaArkayeva
BoMilosz
JanWrangler
HeinrichStroopwafel
HerringoStarr
JosephineMatijevic
AlpaCino
FlintWestwood
DesPaulmond
WilmaJackson
KatrinVerson
BertRandrussell
CarmenNaburana
JimmyTulp
LuisWolf
MirandaFanucci

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.