search
Search
Login
Unlock 100+ guides
menu
menu
web
search toc
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
What does this mean?
Why is this true?
Give me some examples!
search
keyboard_voice
close
Searching Tips
Search for a recipe:
"Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to

Difference between INNER JOIN, LEFT JOIN and RIGHT JOIN in MySQL

schedule Aug 12, 2023
Last updated
local_offer
MySQL
Tags
tocTable of Contents
expand_more
mode_heat
Master the mathematics behind data science with 100+ top-tier guides
Start your free 7-days trial now!

INNER JOIN

MySQL's INNER JOIN clause retrieves rows from both tables where there is a common matching column value.

Consider the very simplistic example where we have the following two tables:

Left table:

person_id

first_name

1

Bob

2

Mary

3

Craig

Right table:

person_id

last_name

1

Builder

2

Poppins

Table that results from joining the two tables using INNER JOIN on the common column person_id:

person_id

first_name

last_name

1

Bob

Builder

2

Mary

Poppins

  • As we have matching values of person_id in both the left and right table (both tables have person_id=1 and person_id=2), we retrieve the first_name and last_name for each of these two people.

  • For person_id=3 this only exists in the left table and there is no match in the right table, hence person_id=3 is not included in the table that results from the INNER JOIN.

LEFT JOIN

MySQL's LEFT JOIN clause returns all rows from the left table and records that match from the right table. NULL will be used for records that have no matching record in the right table.

Consider again the very simplistic example where we have the following two tables:

Left table:

person_id

first_name

1

Bob

2

Mary

3

Craig

Right table:

person_id

last_name

1

Builder

2

Poppins

Table that results from joining the two tables using LEFT JOIN on the common column person_id:

person_id

first_name

last_name

1

Bob

Builder

2

Mary

Poppins

3

Craig

NULL

  • Here we retrieve all rows from the left table (i.e. person_id=1, person_id=2, person_id=3) regardless of whether they have a matching row in the right table.

  • Next we retrieve the corresponding last_name for each person_id from the right table.

  • As person_id=3 does not have a corresponding last_name in the right table, the value is populated as NULL.

RIGHT JOIN

MySQL's RIGHT JOIN clause returns all rows from the right table and matching records from the left table. NULL will be used for records that have no matching record in the left table.

Consider again the very simplistic example where we have the following two tables:

Left table:

person_id

first_name

1

Bob

2

Mary

Right table:

person_id

last_name

1

Builder

2

Poppins

3

Grey

Table that results from joining the two tables using RIGHT JOIN on the common column person_id:

person_id

first_name

last_name

1

Bob

Builder

2

Mary

Poppins

3

NULL

Grey

  • Here we retrieve all rows from the right table (i.e. person_id=1, person_id=2, person_id=3) regardless of whether they have a matching row in the left table.

  • Next we retrieve the corresponding first_name for each person_id from the left table.

  • As person_id=3 does not have a corresponding first_name in the left table, the value is populated as NULL.

robocat
Published by Arthur Yanagisawa
Edited by 0 others
Did you find this page useful?
thumb_up
thumb_down
Comment
Citation
Ask a question or leave a feedback...
thumb_up
0
thumb_down
0
chat_bubble_outline
0
settings
Enjoy our search
Hit / to insta-search docs and recipes!