SQL JOIN without ON in MySQL

1. Omit the ON clause from the JOIN statement

In MySQL, it’s possible to have a JOIN statement without ON as ON is an optional clause. You can just simplly JOIN two tables like this:

SELECT * FROM table_a JOIN table_b;

It will match each row from table_a to every row in table_b. It’s similar to run SELECT * FROM multiple tables statement below.

2. SELECT * FROM multiple tables

This statement is to combine all rows from multiple tables into one table only.

For example, we have table sales:

item	customer_name
book	Alex
pen	Bob

We have another table called upcoming_movies:

movie_name	release_year	universe
Joker	2019	DC
The New Mutants	2020	Marvel

Run the SELECT statement to extract data from two tables:

SELECT * FROM sales, upcoming_movies;

And the results are:

item	customer_name	movie_name	release_year	universe
book	Alex	Joker	2019	DC
pen	Bob	Joker	2019	DC
book	Alex	The New Mutants	2020	Marvel
pen	Bob	The New Mutants	2020	Marvel

Let’s say we have one table with 100 rows, another table with 200 rows, by running the statement above, we get a result of 20,000 rows!

So if you have a common field, use WHERE to narrow down the results.

For the example above, each table has one more id column:

Table sales:

id	item	customer_name
1	book	Alex
2	pen	Bob

Table upcoming_movies:

id	movie_name	release_year	universe
1	Joker	2019	DC
2	The New Mutants	2020	Marvel

Now run SELECT from two tables:

SELECT * FROM sales, upcoming_movies WHERE sales.id = upcoming_movies.id;

The results will be:

id	item	customer_name	id	movie_name	release_year	universe
1	book	Alex	1	Joker	2019	DC
2	pen	Bob	2	The New Mutants	2020	Marvel

3. Use CROSS JOIN

We have table city:

city_id	city	country_id	last_update
251	Kabul	1	2006-02-15 04:45:25
59	Batna	2	2006-02-15 04:45:25
63	Bchar	2	2006-02-15 04:45:25
483	Skikda	2	2006-02-15 04:45:25
516	Tafuna	3	2006-02-15 04:45:25
67	Benguela	4	2006-02-15 04:45:25
360	Namibe	4	2006-02-15 04:45:25
493	South Hill	5	2006-02-15 04:45:25
20	Almirante Brown	6	2006-02-15 04:45:25

And table country:

country_id	country	last_update
1	Afghanistan	2006-02-15 04:44:00
2	Algeria	2006-02-15 04:44:00
3	American Samoa	2006-02-15 04:44:00
4	Angola	2006-02-15 04:44:00
5	Anguilla	2006-02-15 04:44:00
6	Argentina	2006-02-15 04:44:00
7	Armenia	2006-02-15 04:44:00

And we CROSS JOIN two tables:

SELECT * FROM city 
CROSS JOIN country WHERE city.country_id = country.country_id;

The results will be:

city_id	city	country_id	last_update	country_id	country	last_update
251	Kabul	1	2006-02-15 04:45:25	1	Afghanistan	2006-02-15 04:44:00
59	Batna	2	2006-02-15 04:45:25	2	Algeria	2006-02-15 04:44:00
63	Bchar	2	2006-02-15 04:45:25	2	Algeria	2006-02-15 04:44:00
483	Skikda	2	2006-02-15 04:45:25	2	Algeria	2006-02-15 04:44:00
516	Tafuna	3	2006-02-15 04:45:25	3	American Samoa	2006-02-15 04:44:00
67	Benguela	4	2006-02-15 04:45:25	4	Angola	2006-02-15 04:44:00
360	Namibe	4	2006-02-15 04:45:25	4	Angola	2006-02-15 04:44:00
493	South Hill	5	2006-02-15 04:45:25	5	Anguilla	2006-02-15 04:44:00
20	Almirante Brown	6	2006-02-15 04:45:25	6	Argentina	2006-02-15 04:44:00

4. Use JOIN with USING

Still with the example of table city and table country above. To use USING in the JOIN statement:

SELECT * FROM city JOIN country USING (country_id);

The results will be:

country_id	city_id	city	last_update	country	last_update
1	251	Kabul	2006-02-15 04:45:25	Afghanistan	2006-02-15 04:44:00
2	59	Batna	2006-02-15 04:45:25	Algeria	2006-02-15 04:44:00
2	63	Bchar	2006-02-15 04:45:25	Algeria	2006-02-15 04:44:00
2	483	Skikda	2006-02-15 04:45:25	Algeria	2006-02-15 04:44:00
3	516	Tafuna	2006-02-15 04:45:25	American Samoa	2006-02-15 04:44:00
4	67	Benguela	2006-02-15 04:45:25	Angola	2006-02-15 04:44:00
4	360	Namibe	2006-02-15 04:45:25	Angola	2006-02-15 04:44:00
5	493	South Hill	2006-02-15 04:45:25	Anguilla	2006-02-15 04:44:00
6	20	Almirante Brown	2006-02-15 04:45:25	Argentina	2006-02-15 04:44:00

Note that the joining column in both tables must be identically named for USING to work.

5. Use UNION

We have table sales_ytd:

item	customer_name
book	Alex
pen	Bob
book	Alex
book	Jim
shoes	Jim
pen	Bob

And table sales_today:

item	customer_name
backpack	Bob
book	Bob
wallet	Alex
wallet	Alex
book	Bob
backpack	Jim

A UNION of two tables:

SELECT * FROM sales_ytd
UNION 
SELECT * FROM sales_today;

The results will be:

item	customer_name
book	Alex
pen	Bob
book	Alex
book	Jim
shoes	Jim
pen	Bob
backpack	Bob
book	Bob
wallet	Alex
wallet	Alex
book	Bob
backpack	Jim

To use UNION, two tables must match the columns in both number of columns and datatypes

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.