SQL LEFT JOIN
The SQL LEFT JOIN keyword returns all records from the left table (tableA), with the matching records in the right table (tableB).
The query result is NULL in the right side table when there is no match records.
Search Keys
- sql left join
- left join
- difference between left join and left outer join
- left join vs right join
- left join vs inner join
- left join vs left outer join
- left outer join in oracle
- left outer join in sql
- left join sql server
- left join where
- oracle left outer join
- left join example
- sql multiple left joins
- sql update left join
- multiple left joins sql
SQL LEFT JOIN Syntax
SELECT column_name1, column_name2, ...column_nameN
FROM table_name1
LEFT JOIN table_name2
ON table1.column_name1 = table2.column_name1;
or
SELECT column_name1, column_name2, ...column_nameN
FROM table_name1
LEFT OUTER JOIN table_name2
ON table1.column_name1 = table2.column_name1;
Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.
Sample Database Table - Books
BookId |
BookName |
BookPrice |
RelYear |
DomainName |
AuthorName |
2 |
Pro Oracle SQL |
168.27 |
2013 |
Security |
Vidyavathi |
3 |
Sql Server Interview Questions |
125 |
2006 |
Performance |
Nirmala |
4 |
Getting Started With SQL |
115 |
2009 |
Database |
Nirmala |
6 |
The Complete Guide to SQL Server |
140 |
2013 |
Administration |
Balan |
7 |
Securing SQL Server |
136.33 |
2012 |
Security |
Rishi Keshan |
8 |
Art Of SQL |
99.99 |
2007 |
Administration |
Siva Kumar |
9 |
MySQL: The Complete Reference |
105 |
2009 |
Performance |
Rishi Keshan |
11 |
How to Write Accurate SQL Code |
130 |
2007 |
Optimization |
Pandurengan |
Sample Database Table - BookOrder
BookID |
OrderDate |
Qty |
DeliveryDate |
1 |
17-09-1996 |
1 |
26-09-1996 |
2 |
22-03-2004 |
6 |
26-04-2004 |
4 |
17-07-2011 |
2 |
24-08-2011 |
6 |
23-04-1995 |
4 |
26-04-1995 |
8 |
13-08-2008 |
1 |
21-09-2008 |
Note:- There must be a common columns while selecting columns from multiple tables.
In the "BookOrder" and "Books" both tables contains a common column called "ID".
SQL LEFT JOIN - Example
Notice that the "BookID" column in the "Books" table refers to the "BookID" in the "BookOrder" table.
The relationship between the two tables above is the "BookID" column.
Then, if we run the following SQL statement (that contains an LEFT JOIN):
SELECT
Books.BookID, Books.BookName, BookOrder.Qty, BookOrder.OrderDate
FROM Books
LEFT JOIN BookOrder
ON Books.BookID = BookOrder.BookID;
The result of above query is:
BookID |
BookName |
Qty |
OrderDate |
2 |
Pro Oracle SQL |
6 |
22-03-2004 |
3 |
Sql Server Interview Questions |
|
|
4 |
Getting Started With SQL |
2 |
17-07-2011 |
6 |
The Complete Guide to SQL Server |
4 |
23-04-1995 |
7 |
Securing SQL Server |
|
|
8 |
Art Of SQL |
1 |
13-08-2008 |
9 |
MySQL: The Complete Reference |
|
|
11 |
How to Write Accurate SQL Code |
|
|
Note: The SQL LEFT JOIN keyword returns all the records from the left side table (BookOrder), even if there are no matches in the right side table (Books).