search
Search
Join our weekly DS/ML newsletter layers DS/ML Guides
menu
menu search toc more_vert
Robocat
Guest 0reps
Thanks for the thanks!
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
help Ask a question
Share on Twitter
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
A
A
brightness_medium
share
arrow_backShare
Twitter
Facebook
0
thumb_down
0
chat_bubble_outline
0
auto_stories new
settings

My SQL | LEFT JOIN

Database
chevron_right
MySQL
chevron_right
Documentation
chevron_right
Statements and Clauses
schedule Jul 1, 2022
Last updated
local_offer MySQL
Tags
tocTable of Contents
expand_more

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).

mail
Join our newsletter for updates on new DS/ML comprehensive guides (spam-free)
robocat
Published by Arthur Yanagisawa
Edited by 0 others
Did you find this page useful?
Ask a question or leave a feedback...
0
thumb_down
0
chat_bubble_outline
0
settings
Enjoy our search
Hit / to insta-search docs and recipes!