---
description: This tutorial tells about different types of joins, functions of joins and how joins can be useful in retrieving data from more than one table. Left, right, outer, inner, cross join
title: "MySQL Join: Inner, Outer, Left, Right, Cross"
image: https://www.guru99.com/images/mysql-joins-1.png
---

[Skip to content](#main)

**⚡ 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.**

**Don't Miss:**

- [MySQL AUTO\_INCREMENT with Examples](https://www.guru99.com/auto-increment.html "MySQL AUTO_INCREMENT with Examples")
- [MySQL INSERT INTO Query: How to add Row in Table (Example)](https://www.guru99.com/insert-into.html)
- [What is SQL? Full Form & Basics Tutorial](https://www.guru99.com/what-is-sql.html)
- [Top 50 MySQL Interview Questions and Answers (2026)](https://www.guru99.com/mysql-interview-questions.html)

## 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]()](https://www.guru99.com/images/CrossJoin.png)

```
SELECT * FROM `movies` CROSS JOIN `members`
```

Executing the above script in [MySQL workbench](https://www.guru99.com/introduction-to-mysql-workbench.html) 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]()](https://www.guru99.com/images/InnerJoin.png)

```
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](https://www.guru99.com/null.html)** 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]()](https://www.guru99.com/images/LeftOuterJoin.png)

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]()](https://www.guru99.com/images/RightOuterJoin.png)

```
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](https://www.guru99.com/group-by.html), WHERE** and even functions like **SUM**, **AVG**, etc.

## FAQs

🔀 What is the difference between a JOIN and a UNION?

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.

🧮 Can more than two tables be joined in a single query?

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.

🪞 What is a SELF JOIN in MySQL?

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.

🤖 Can AI tools write MySQL JOIN queries automatically?

Yes. AI assistants built into editors such as [MySQL Workbench](https://www.guru99.com/introduction-to-mysql-workbench.html) can draft JOIN queries from a plain-language prompt. Always review the generated ON conditions, since an incorrect key produces silently wrong results.

⚙️ Does AI-based query optimization improve JOIN performance?

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:

ChatGPTPerplexityGrokGoogle AI

**Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.**

##### Sign up for the newsletter

Subscribe for Free

You have successfully subscribed.  
Please check your inbox.

![AI-Newsletter]() Chosen by over **350,000+** professionals

[Scroll to top](#wrapper)

×

Search for:

```json
{"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/mysql-joins-1.png","url":"https://www.guru99.com/images/mysql-joins-1.png","width":"700","height":"250","caption":"MySQL JOINS","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/joins.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/sql","name":"SQL"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/joins.html","name":"MySQL Join: Inner, Outer, Left, Right, Cross"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/joins.html#webpage","url":"https://www.guru99.com/joins.html","name":"MySQL Join: Inner, Outer, Left, Right, Cross","dateModified":"2026-10-07T16:20:05+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/mysql-joins-1.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/joins.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/editorial","name":"Editorial Team","url":"https://www.guru99.com/author/editorial","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/favicon-new-logo.png","url":"https://www.guru99.com/images/favicon-new-logo.png","caption":"Editorial Team","inLanguage":"en-US"},"sameAs":["https://www.guru99.com/editorial-team"],"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"articleSection":"SQL","headline":"MySQL Join: Inner, Outer, Left, Right, Cross","description":"This tutorial tells about different types of joins, functions of joins and how joins can be useful in retrieving data from more than one table. Left, right, outer, inner, cross join","keywords":"sql","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/editorial","name":"Editorial Team"},"dateModified":"2026-10-07T16:20:05+05:30","image":{"@id":"https://www.guru99.com/images/mysql-joins-1.png"},"copyrightYear":"2026","name":"MySQL Join: Inner, Outer, Left, Right, Cross","subjectOf":[{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"What is the difference between a JOIN and a UNION?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Can more than two tables be joined in a single query?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"What is a SELF JOIN in MySQL?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Can AI tools write MySQL JOIN queries automatically?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Does AI-based query optimization improve JOIN performance?","acceptedAnswer":{"@type":"Answer","text":"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."}}]}],"@id":"https://www.guru99.com/joins.html#schema-1144090","isPartOf":{"@id":"https://www.guru99.com/joins.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/joins.html#webpage"}}]}
```