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.

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


Search Keys

  • left join two tables
  • sql left join 3 tables
  • t sql left outer join
  • sql left join select
  • explain sql joins with example
  • sql tutorial joins
  • left outer join in pl sql
  • natural left outer join
  • update left outer join
  • natural left join
  • example of left outer join
  • sql left join query
  • multiple left outer join sql
  • left join with example
  • sql left join performance