MySQL Join: Inner, Outer, Left, Right, Cross

By : Marcus Allen Marcus Allen
Hours Updated

⚡ Smart Summary

MySQL JOINS combine rows from two or more related tables into a single result set. This resource explains CROSS, INNER, LEFT, RIGHT, and OUTER JOINS with runnable queries, sample data, and clear output tables for practical database work.

  • 🔗 Core Principle: A JOIN matches rows across tables using primary key and foreign key relationships.
  • ⚡ Why It Matters: One JOIN query uses indexing and reduces server round trips versus several separate queries.
  • ✖️ Cross JOIN Behaviour: Every row of the first table pairs with every row of the second, producing a Cartesian product.
  • 🎯 Inner JOIN Behaviour: Only rows satisfying the matching condition in both tables are returned.
  • ↔️ Outer JOIN Behaviour: LEFT and RIGHT JOIN also return unmatched rows and fill the missing columns with NULL.
  • 🧩 ON versus USING: USING requires identical column names, while ON supports any matching expression.
Read More

MySQL JOINS

What are JOINS?

Joins help retrieving data from two or more database tables.

The tables are mutually related using primary and foreign keys.

Note: JOIN is the most misunderstood topic amongst SQL leaners. For sake of simplicity and ease of understanding, we will be using a new Database to practice sample. As shown below

Every example below uses these two tables. The movie_id column in members points to the id column in movies — the relationship each JOIN matches on.

members

id first_name last_name movie_id
1 Adam Smith 1
2 Ravi Kumar 2
3 Susan Davidson 5
4 Jenny Adrianna 8
5 Lee Pong 10

movies

id title category
1 ASSASSIN’S CREED: EMBERS Animations
2 Real Steel(2012) Animations
3 Alvin and the Chipmunks Animations
4 The Adventures of Tin Tin Animations
5 Safe (2012) Action
6 Safe House(2012) Action
7 GIA 18+
8 Deadline 2009 18+
9 The Dirty Picture 18+
10 Marley and me Romance

Why should we use JOINS?

Before looking at each JOIN type, it is worth knowing why a JOIN is preferred over running several queries.

Now you may think, why we use JOINs when we can do the same task running queries. Especially if you have some experience in database programming you know we can run queries one by one, use output of each in successive queries. Of course, that is possible. But using JOINs, you can get the work done by using only a one query with any search parameters. On the other hand MySQL can achieve better performance with JOINs as it can use Indexing. Simply use of single JOIN query instead running multiple queries do reduce server overhead. Using multiple queries instead that leads more data transfers between MySQL and applications (software). Further it requires more data manipulations in application end also.

It is clear that we can achieve better MySQL and application performances by use of JOINs.

RELATED ARTICLES

Types of JOINS

MySQL supports several JOIN types, each answering a different question about the same two tables. The table below compares them; each type is then demonstrated with a query and its output.

JOIN type Rows returned NULLs in result? Typical use
CROSS JOIN Every row of table A paired with every row of table B No Generating all possible combinations
INNER JOIN Only rows matching the condition in both tables No Members who actually rented a movie
LEFT JOIN All rows from the left table, plus matches from the right Yes, on the right side All movies, even those never rented
RIGHT JOIN All rows from the right table, plus matches from the left Yes, on the left side All movies, even with no member attached

CROSS JOIN

Cross JOIN is a simplest form of JOINs which matches each row from one database table to all rows of another.

In other words it gives us combinations of each row of first table with all records in second table.

Suppose we want to get all member records against all the movie records, we can use the script shown below to get our desired results.

Types of joins

SELECT * FROM `movies` CROSS JOIN `members`

Executing the above script in MySQL workbench gives us the following results.





id
title

id
first_name
last_name
movie_id






1
ASSASSIN'S CREED: EMBERS
Animations
1
Adam
Smith
1




1
ASSASSIN'S CREED: EMBERS
Animations
2
Ravi
Kumar
2




1
ASSASSIN'S CREED: EMBERS
Animations
3
Susan
Davidson
5




1
ASSASSIN'S CREED: EMBERS
Animations
4
Jenny
Adrianna
8




1
ASSASSIN'S CREED: EMBERS
Animations
6
Lee
Pong
10




2
Real Steel(2012)
Animations
1
Adam
Smith
1




2
Real Steel(2012)
Animations
2
Ravi
Kumar
2




2
Real Steel(2012)
Animations
3
Susan
Davidson
5




2
Real Steel(2012)
Animations
4
Jenny
Adrianna
8




2
Real Steel(2012)
Animations
6
Lee
Pong
10




3
Alvin and the Chipmunks
Animations
1
Adam
Smith
1




3
Alvin and the Chipmunks
Animations
2
Ravi
Kumar
2




3
Alvin and the Chipmunks
Animations
3
Susan
Davidson
5




3
Alvin and the Chipmunks
Animations
4
Jenny
Adrianna
8




3
Alvin and the Chipmunks
Animations
6
Lee
Pong
10




4
The Adventures of Tin Tin
Animations
1
Adam
Smith
1




4
The Adventures of Tin Tin
Animations
2
Ravi
Kumar
2




4
The Adventures of Tin Tin
Animations
3
Susan
Davidson
5




4
The Adventures of Tin Tin
Animations
4
Jenny
Adrianna
8




4
The Adventures of Tin Tin
Animations
6
Lee
Pong
10




5
Safe (2012)
Action
1
Adam
Smith
1




5
Safe (2012)
Action
2
Ravi
Kumar
2




5
Safe (2012)
Action
3
Susan
Davidson
5




5
Safe (2012)
Action
4
Jenny
Adrianna
8




5
Safe (2012)
Action
6
Lee
Pong
10




6
Safe House(2012)
Action
1
Adam
Smith
1




6
Safe House(2012)
Action
2
Ravi
Kumar
2




6
Safe House(2012)
Action
3
Susan
Davidson
5




6
Safe House(2012)
Action
4
Jenny
Adrianna
8




6
Safe House(2012)
Action
6
Lee
Pong
10




7
GIA
18+
1
Adam
Smith
1




7
GIA
18+
2
Ravi
Kumar
2




7
GIA
18+
3
Susan
Davidson
5




7
GIA
18+
4
Jenny
Adrianna
8




7
GIA
18+
6
Lee
Pong
10




8
Deadline(2009)
18+
1
Adam
Smith
1




8
Deadline(2009)
18+
2
Ravi
Kumar
2




8
Deadline(2009)
18+
3
Susan
Davidson
5




8
Deadline(2009)
18+
4
Jenny
Adrianna
8




8
Deadline(2009)
18+
6
Lee
Pong
10




9
The Dirty Picture
18+
1
Adam
Smith
1




9
The Dirty Picture
18+
2
Ravi
Kumar
2




9
The Dirty Picture
18+
3
Susan
Davidson
5




9
The Dirty Picture
18+
4
Jenny
Adrianna
8




9
The Dirty Picture
18+
6
Lee
Pong
10




10
Marley and me
Romance
1
Adam
Smith
1




10
Marley and me
Romance
2
Ravi
Kumar
2




10
Marley and me
Romance
3
Susan
Davidson
5




10
Marley and me
Romance
4
Jenny
Adrianna
8




10
Marley and me
Romance
6
Lee
Pong
10

INNER JOIN

A CROSS JOIN returns every possible pairing, which is rarely what you want. An INNER JOIN narrows the result down to the pairs that are actually related.

The inner JOIN is used to return rows from both tables that satisfy the given condition.

Suppose, you want to get list of members who have rented movies together with titles of movies rented by them. You can simply use an INNER JOIN for that, which returns rows from both tables that satisfy with given conditions.

INNER JOIN

SELECT members.`first_name` , members.`last_name` , movies.`title`
FROM members ,movies
WHERE movies.`id` = members.`movie_id`

Executing the above script give





first_name
last_name
title






Adam
Smith
ASSASSIN'S CREED: EMBERS




Ravi
Kumar
Real Steel(2012)




Susan
Davidson
Safe (2012)




Jenny
Adrianna
Deadline(2009)




Lee
Pong
Marley and me

Note the above results script can also be written as follows to achieve the same results.

SELECT A.`first_name` , A.`last_name` , B.`title`
FROM `members` AS A
INNER JOIN `movies` AS B
ON B.`id` = A.`movie_id`

Outer JOINs

An INNER JOIN silently drops rows that have no partner. When those unmatched rows matter, an OUTER JOIN is the right choice.

MySQL Outer JOINs return all records matching from both tables.

It can detect records having no match in joined table. It returns NULL values for records of joined table if no match is found.

Sounds Confusing? Let’s look into an example –

LEFT JOIN

Assume now you want to get titles of all movies together with names of members who have rented them. It is clear that some movies have not being rented by any one. We can simply use LEFT JOIN for the purpose.

Outer JOINs

The LEFT JOIN returns all the rows from the table on the left even if no matching rows have been found in the table on the right. Where no matches have been found in the table on the right, NULL is returned.

SELECT A.`title` , B.`first_name` , B.`last_name`
FROM `movies` AS A
LEFT JOIN `members` AS B
ON B.`movie_id` = A.`id`

Executing the above script in MySQL workbench gives. You can see that in the returned result which is listed below that for movies which are not rented, member name fields are having NULL values. That means no matching member found members table for that particular movie.





title
first_name
last_name






ASSASSIN'S CREED: EMBERS
Adam
Smith




Real Steel(2012)
Ravi
Kumar




Safe (2012)
Susan
Davidson




Deadline(2009)
Jenny
Adrianna




Marley and me
Lee
Pong




Alvin and the Chipmunks
NULL
NULL




The Adventures of Tin Tin
NULL
NULL




Safe House(2012)
NULL
NULL




GIA
NULL
NULL




The Dirty Picture
NULL
NULL





Note: Null is returned for non-matching rows on right

RIGHT JOIN

RIGHT JOIN is obviously the opposite of LEFT JOIN. The RIGHT JOIN returns all the columns from the table on the right even if no matching rows have been found in the table on the left. Where no matches have been found in the table on the left, NULL is returned.

In our example, let’s assume that you need to get names of members and movies rented by them. Now we have a new member who has not rented any movie yet

RIGHT JOIN

SELECT A.`first_name` , A.`last_name`, B.`title`
FROM `members` AS A
RIGHT JOIN `movies` AS B
ON B.`id` = A.`movie_id`

Executing the above script in MySQL workbench gives the following results.





first_name
last_name
title






Adam
Smith
ASSASSIN'S CREED: EMBERS




Ravi
Kumar
Real Steel(2012)




Susan
Davidson
Safe (2012)




Jenny
Adrianna
Deadline(2009)




Lee
Pong
Marley and me




NULL
NULL
Alvin and the Chipmunks




NULL
NULL
The Adventures of Tin Tin




NULL
NULL
Safe House(2012)




NULL
NULL
GIA




NULL
NULL
The Dirty Picture





Note: Null is returned for non-matching rows on left

“ON” and “USING” clauses

Every query so far has matched rows with an ON clause. MySQL offers a shorter alternative when the matched columns share a name.

In above JOIN query examples, we have used ON clause to match the records between table.

USING clause can also be used for the same purpose. The difference with USING is it needs to have identical names for matched columns in both tables.

In “movies” table so far we used its primary key with the name “id”. We referred to same in “members” table with the name “movie_id”.

Let’s rename “movies” tables “id” field to have the name “movie_id”. We do this in order to have identical matched field names.

ALTER TABLE `movies` CHANGE `id` `movie_id` INT( 11 ) NOT NULL AUTO_INCREMENT;

Next let’s use USING with above LEFT JOIN example.

SELECT A.`title` , B.`first_name` , B.`last_name`
FROM `movies` AS A
LEFT JOIN `members` AS B
USING ( `movie_id` )

Apart from using ON and USING with JOINs you can use many other MySQL clauses like GROUP BY, WHERE and even functions like SUM, AVG, etc.

FAQs

A JOIN combines columns from two tables side by side, matching rows on a key. A UNION stacks the results of two queries vertically and requires matching column counts and types.

Yes. Chain additional JOIN clauses, each with its own ON condition. MySQL joins the first two tables, then joins that intermediate result to the next table, and so on.

A SELF JOIN joins a table to itself using two aliases. It compares rows inside one table, such as matching an employee row to the row of that employee’s manager.

Yes. AI assistants built into editors such as MySQL Workbench can draft JOIN queries from a plain-language prompt. Always review the generated ON conditions, since an incorrect key produces silently wrong results.

Partly. AI advisors suggest indexes and better join orders, which often cut execution time. The optimizer still picks the final plan, so correct indexing on the join keys remains the largest factor.

Summarize this post with: