{"id":13775,"date":"2023-12-29T01:25:25","date_gmt":"2023-12-29T08:25:25","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=13775"},"modified":"2024-01-14T18:59:52","modified_gmt":"2024-01-15T01:59:52","slug":"mysql-left-join","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-left-join\/","title":{"rendered":"MySQL LEFT JOIN"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about MySQL LEFT JOIN<\/code> clause and how to use it to retrieve data from two or more related tables.<\/p>\n\n\n\n

Introduction to MySQL LEFT JOIN clause<\/h2>\n\n\n\n

The LEFT JOIN<\/code> allows you to retrieve data from two or more tables. Like the INNER JOIN<\/a><\/code> clause, the LEFT JOIN<\/code> is an optional clause of the SELECT<\/a><\/code> statement, which appears immediately after the FROM<\/code> clause.<\/p>\n\n\n\n

The following statement illustrates how to use the LEFT JOIN<\/code> clause to join the two tables, t1<\/code> and t2<\/code>:<\/p>\n\n\n

SELECT<\/span> \n    select_list\nFROM<\/span>\n    t1\nLEFT<\/span> JOIN<\/span> t2 ON<\/span> \n    join_condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
When you use the LEFT JOIN<\/code> clause, the concepts of the left table (t1) and the right table (t2) come into play within the syntax.<\/p>\n\n\n\n
The LEFT JOIN<\/code> clause selects data starting from the left table (t1<\/code>), matching each row from the left table (t1<\/code>) with every corresponding row from the right table(t2<\/code>) based on the join_condition<\/code>.<\/p>\n\n\n\n
If the rows from both tables satisfy the join condition, the left join combines columns from both tables into a new row and includes this new row in the result rows.<\/p>\n\n\n\n
If a row from the left table (t1<\/code>) does not match with any row from the right table(t2<\/code>), the left join still combines columns of rows from both tables into a new row and includes the new row in the result set. However, it fills in the columns of the row from the right table with the NULL<\/code> values.<\/p>\n\n\n\n
In essence, the LEFT JOIN<\/code> returns all rows from the left table, irrespective of whether a matching row from the right table exists or not. <\/p>\n\n\n\n
In the absence of a match, the columns of the row from the right table will be filled with NULL<\/code> values.<\/p>\n\n\n\n
The following Venn diagram helps you visualize how the LEFT JOIN<\/code> clause works:<\/p>\n\n\n
\n
\"MySQL
MySQL LEFT JOIN – Venn Diagram<\/figcaption><\/figure>\n<\/div>\n\n\n
MySQL LEFT JOIN clause examples<\/h2>\n\n\n\n
Let’s take some examples of using the LEFT JOIN<\/code> clause.<\/p>\n\n\n\n
1) Using MySQL LEFT JOIN clause to join two tables<\/h3>\n\n\n\n
See the following tables customers<\/code> and orders<\/code> in the sample database<\/a>.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Each customer can have zero or more orders, whereas each order must belong to one customer.<\/p>\n\n\n\n
The following query uses the LEFT JOIN<\/code> clause to find all customers and their corresponding orders:<\/p>\n\n\n
SELECT<\/span> \n    customers.customerNumber, \n    customerName, \n    orderNumber, \n    status<\/span>\nFROM<\/span>\n    customers\nLEFT<\/span> JOIN<\/span> orders ON<\/span> \n    orders.customerNumber = customers.customerNumber;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span>\n    c.customerNumber,\n    customerName,\n    orderNumber,\n    status<\/span>\nFROM<\/span>\n    customers c\nLEFT<\/span> JOIN<\/span> orders o \n    ON<\/span> c.customerNumber = o.customerNumber;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
In this example:<\/p>\n\n\n\n
Alternatively, you can make the query shorter using table aliases<\/a>:<\/p>\n\n\n