I wanted to write a quick blog on LEFT JOIN because I get so many questions about this topic from young DB Admin’s and developers as well as Business Managers.
Define
In SQL the LEFT JOIN keyword will return all records from the left table (table a), and the matched records from the right table (table b). The result is NULL from the right side, if there is no match.
LEFT JOIN Syntax |
SELECT column_names(s) FROM table_a LEFT JOIN table_b ON table_a.column_name = tableb.column_name |
Instructional
In this instruction we will use two tables to demonstrate using LEFT JOIN within a SQL statement. Lets first define two database tables as well as include the data within those tables.
User table
Id | Name | Address | Zip_Code |
5 | Bob Vance | 45 Geear St | 12345 |
6 | Dwight Schrute | 55 Sweet St | 65565 |
7 | Jim Halbret | 65 Nice Rd | 43455 |
Order Table
Id | User_Id | Employee_Id | Order_Date |
3 | 5 | 67 | 2019-01-01 |
45 | 15 | 89 | 2019-01-01 |
55 | 16 | 34 | 2019-01-01 |
Example
The following SQL statement will select all users, and any orders they might have.
SQL Demo |
SELECT User.Name, Order.Id FROM User LEFT JOIN Order ON User.Id = Order.User_Id ORDER BY User.Name DESC; |