LEFT OUTER JOIN in SQL

Introduction to LEFT OUTER JOIN in SQL

In SQL, the Left OUTER JOIN is the same as the LEFT JOIN where we can combine two tables on a certain condition. By definition, SQL Left Outer Join keyword executes to fetch all the rows from the left table (suppose Table A) along with some common rows if matched from the right table (Suppose Table B) form the two tables. If there is no common row that matches between left and right tables then the result is NULL, the Left Outer Join returns only the left tables data.

We can say that it is a SQL Inner Join with the unmatched records from the left table too. Hence, it. produces the same result as Left Join i.e. they are treated the same in some databases as they perform the same task to return all rows from the left table and matched rows from the right table.

Syntax:

Let us consider two tables A & B.

SELECT Column1,..,ColumnN From TableA LEFT JOIN TableB ON join conditional expression WHERE row condition expression;

Fig: Pictorial Representation of LEFT OUTER JOIN

For example, we have two tables Customer ( CustID, Name, Age, Price ) and Orders ( OID, CustID, Amount ):

If we apply IT on this then, the SQL statement goes like.

SELECT Customer.Name, Orders.Od_ID From Customer LEFT Outer JOIN Orders ON Customer.CustID = Orders.CustID ORDER BY Customer.Name;

Here, we have selected the Name column from the Customer table and OID from Orders table where we have selected those rows from both the tables which have a common column that is CustID and all other rows from the left table i.e. Customer table. If there is no match in the right table for the left row table then, the column is set to be NULL.

However, SQL LEFT JOIN and SQL LEFT OUTER JOIN to deliver identical results by using the SQL statements.

How LEFT OUTER JOIN works in SQL?

• In Oracle join operator (+) helps to perform SQL Outer Joins on two or multiple tables.
• Normally, in SQL when we are combing two tables and want to get the result set providing the unmatched rows from only one table that is specified before the OUTER JOIN then, SQL LEFT OUTER JOIN or SQL RIGHT OUTER JOIN is the best option to be used.
• It returns all the records from the left table even if there is no common row matched between the two tables.
• It means that if the ON joins condition does not find any rows that match then, it includes the NULL values for the columns from the right table after comparing all the rows from both the tables from the query executed in SQL.

Examples of LEFT OUTER JOIN in SQL

The following examples illustrate the uses of SQL LEFT OUTER JOIN to combine two tables:

Let us consider two Tables “CompTable” and “FoodTable”. Creating the tables by the below SQL queries:

Code:

CREATE TABLE CompTable (CompanyID int NOT NULL PRIMARY KEY,
CompanyName varchar(255) NOT NULL, CompanyCity varchar(255) );

CREATE TABLE FoodTable ( ItemID int NOT NULL PRIMARY KEY ItemName varchar(255) NOT NULL, ItemUnit varchar(255),
CompanyID int );

Also, let us enter some data into the tables with the following SQL statements and we will get the table as below using SELECT statement:

Code:

INSERT INTO Company (CompanyID, CompanyName, CompanyCity)
VALUES ('1', 'Parle G', 'Delhi');

Output:

Code:

INSERT INTO FoodTable (ItemID, ItemName, ItemUnit, CompanyID)
VALUES ('11', 'Biscuit', 'Pcs', '1');

Output:

Example #1 – SQL LEFT OUTER JOIN Keyword

We consider the above two tables. Now, we apply LEFT OUTER JOIN on the Tables CompTable and FoodTable to get all the records from the left table and some common rows between the left and right tables.

The result set contains all the records of left CompTable and the rows that are matched with right FoodTable and if not matched any columns then it is represented with the NULL column.

Code:

SELECT CompTable.CompanyID, CompTable.CompanyName, CompTable.CompanyCity, FoodTable.CompanyID, FoodTable.ItemName FROM CompTable LEFT OUTER JOIN FoodTable ON CompTable.CompanyID = FoodTable.CompanyID;

Output:

Example #2 – WHERE keyword and Condition

Code:

ELECT CompTable.CompanyID, CompTable.CompanyName, CompTable.CompanyCity, FoodTable.CompanyID, FoodTable.ItemName FROM CompTable LEFT OUTER JOIN FoodTable ON CompTable.CompanyID = FoodTable.CompanyID WHERE CompTable.CompanyID > 2;

Here, we have applied LEFT JOIN on the same tables but with the SQL WHERE keyword and adding a condition that the CompanyID from CompTable table should be greater than 2.

Output:

Example #3 – ORDER BY clause

Again, we have used SQL LEFT OUTER JOIN to fetch the data from both tables by combing them with some common values, NULL values, and including one more field ItemName in the result set.

Code:

SELECT CompTable.CompanyID, CompTable.CompanyName, CompTable.CompanyCity, FoodTable.CompanyID, FoodTable.ItemName, FoodTable.ItemID FROM CompTable LEFT OUTER JOIN FoodTable
ON CompTable.CompanyID = FoodTable.CompanyID ORDER BY CompTable.CompanyName ASC;

But one more thing that is we have used the ORDER BY clause here in the query to produce the result of LEFT JOIN in the ascending order of CompanyID form the CompTable. So, we get the following result as follows for the above SQL statement.

Output:

Conclusion

Thus, we have learned about it which is useful to combine two or more tables in databases to provide the result from the left tables and with the common results from the right table if matched any rows. If no row matches with the right one then, NULL values are used. SQL LEFT JOIN allows you to query data from multiple tables by comparing the tables based on the JOIN keywords condition into a single table. This provides many one-to-many relationships and in the end, it gives the result set with massive data in the tables also including mostly duplicates.

This has been a guide to LEFT OUTER JOIN in SQL. Here we discuss the introduction, examples and how LEFT OUTER JOIN works in SQL? You may also have a look at the following articles to learn more –

1. SQL Clauses
2. LIKE Query in SQL
3. Types of NoSQL Databases
4. Condition in MySQL