search
Search
Login
Map of Data Science
menu
menu
search toc
Thanks for the thanks!
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
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

My SQL | LEFT JOIN

schedule Mar 5, 2023
Last updated
local_offer
MySQL
Tags
tocTable of Contents
expand_more
map
Check out the interactive map of data science

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. LEFT JOIN is also referred to as LEFT OUTER JOIN.

Syntax

SELECT column_name(s)
FROM left_table
LEFT JOIN right_table
ON left_table.column = right_table.column;

Examples

Consider the following table about products bought by some customers:

id

product

bought_by

1

computer

1

2

hair dryer

3

The above sample table can be created using the code here.

Also consider the following table containing customer information:

id

name

age

1

alex

15

2

bob

25

3

cathy

30

The above sample table can be created using the code here.

Basic usage

To perform a LEFT JOIN with customer as the main (left) table and product as the secondary (right) table:

SELECT c.id, c.name, c.age, p.name AS product_name
FROM customer c
LEFT JOIN product p
ON c.id = p.bought_by;
+----+-----------+------+--------------+
| id | name | age | product_name |
+----+-----------+------+--------------+
| 1 | alex | 15 | computer |
| 3 | cathy | 30 | hair dryer |
| 2 | bob | 25 | NULL |
+----+-----------+------+--------------+

Note that we return all three records from the left table customer and matching records from the right table (i.e. where value of id in customer table is equal to value of bought_by in products table). As bob did not have any matching record in the right table (he did not buy anything!) NULL is returned.

NOTE

We can also perform LEFT JOIN using more than two tables. However, there will always be one main table (all its rows will be present in the result), while the others will be secondary (rows will only appear if there are matches else 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!