EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

LEFT OUTER JOIN in SQL

By Aanchal SharmaAanchal Sharma

Home » Data Science » Data Science Tutorials » SQL Tutorial » LEFT OUTER JOIN in SQL

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax:

Let us consider two tables A & B.

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

LEFT OUTER JOIN in SQL 7

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:

LEFT OUTER JOIN in SQL 2

Code:

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

Output:

LEFT OUTER JOIN in SQL 3

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:

LEFT OUTER JOIN in SQL 4

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:

Where keyword and condition

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:

ORDER BY CLAUSE

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.

Recommended Articles

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

SQL Training Program (7 Courses, 8+ Projects)

7 Online Courses

8 Hands-on Projects

73+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
SQL Tutorial
  • Joins
    • Join Query in SQL
    • Types of Joins in SQL
    • Types of Joins in SQL Server
    • SQL Inner Join
    • SQL Join Two Tables
    • SQL Delete Join
    • SQL Left Join
    • LEFT OUTER JOIN in SQL
    • SQL Right Join
    • SQL Cross Join
    • SQL Outer Join
    • SQL Full Join
    • SQL Self Join
    • Natural Join SQL
    • SQL Multiple Join
  • Basic
    • What is SQL
    • Careers in SQL
    • Careers in SQL Server
    • IS SQL Microsoft?
    • SQL Management Tools
    • What is SQL Developer
    • Uses of SQL
    • How to Install SQL Server
    • What is SQL Server
    • SQL Server Versions
    • SQL Case Insensitive
    • SQL Expressions
    • Database in SQL
    • SQL Data Types
    • SQL Keywords
    • Composite Key in SQL
    • SQL WAITFOR
    • SQL Constraints
    • Transactions in SQL
    • First Normal Form
    • SQL Server Data Types
    • SQL Administration
    • SQL Variables
    • SQL Enum
    • SQL GROUP BY WHERE
    • SQL ROW
    • SQL EXECUTE
    • SQL EXCLUDE
    • SQL Performance Tuning
    • SQL UUID
    • Begin SQL
    • SQL Update Join
    • Cheat sheet SQL
  • Operators
    • SQL Operators
    • SQL Arithmetic Operators
    • SQL Logical Operators
    • SQL String Operators
    • Ternary Operator in SQL
  • Commands
    • SQL Commands
    • sqlplus set commands
    • SQL Alter Command
    • SQL Commands Update
    • SQL DML Commands
    • SQL DDL Commands
    • FETCH in SQL
  • Clause
    • SQL Clauses
    • SQL IN Operator
    • SQL LIKE Clause
    • SQL NOT Operator
    • SQL Minus
    • SQL WHERE Clause
    • SQL with Clause
    • SQL HAVING Clause
    • GROUP BY clause in SQL
    • SQL GROUP BY DAY
    • ORDER BY Clause in SQL
    • SQL ORDER BY CASE
    • SQL ORDER BY DESC
    • SQL ORDER BY DATE
    • SQL ORDER BY Alphabetical
    • SQL ORDER BY Ascending
    • SQL Order by Count
    • SQL GROUP BY Month
    • SQL GROUP BY Multiple Columns
    • SQL GROUPING SETS
  • Queries
    • SQL Insert Query
    • SQL SELECT Query
    • SQL SELECT RANDOM
    • SQL Except Select
    • SQL Subquery
    • SQL SELECT DISTINCT
    • SQL WITH AS Statement
  • Keys
    • SQL Keys
    • Primary Key in SQL
    • Foreign Key in SQL
    • Unique Key in SQL
    • Alternate Key in SQL
    • SQL Super Key
  • Functions
    • SQL Date Function
    • SQL String Functions
    • SQL Compare String
    • Timestamp to Date in SQL
    • SQL Window Functions
    • SQL CONCAT
    • SQL ALTER TABLE
    • SQL MOD()
    • SQL Timestamp
    • SQL TO_DATE()
    • SQL DATEADD()
    • SQL DATEDIFF()
    • SQL HOUR()
    • SQLite?functions
    • ANY in SQL
    • LIKE Query in SQL
    • SQL NOT NULL
    • SQL NOT IN
    • SQL MAX()
    • SQL MIN()
    • SQL SUM()
    • SQL COUNT
    • SQL identity
    • SQL DELETE Trigger
    • SQL Declare Variable
    • SQL Text Search
    • SQL COUNT DISTINCT
    • SQL TEXT
    • SQL Limit Order By
    • BETWEEN in SQL
    • LTRIM() in SQL
    • TOP in SQL
    • SQL Select Top
    • Merge SQL
    • SQL TRUNCATE()
    • SQL UNION
    • SQL ALL
    • SQL INTERSECT
    • SQL Alias
    • SQL Server Substring
    • CUBE in SQL
    • SQL RANK()
    • SQL MOD()
    • SQL CTE
    • SQL LAG()
    • SQL MID
    • SQL avg()
    • SQL WEEK
    • SQL DELETE
    • SQL DATEPART()
    • SQL DECODE()
    • SQL DENSE_RANK()
    • SQL NTILE()
    • SQL NULLIF()
    • SQL Stuff
    • SQL Ceiling
    • SQL EXISTS
    • SQL LEAD()
    • SQL COALESCE
    • SQL BLOB
    • SQL ROW_NUMBER
    • SQL Server Replace
    • SQL Server Permission
    • T-SQL INSERT
    • T-SQL Stuff
    • T-SQL ADD Column
    • SQL Ranking Function
  • Advanced
    • SQL Formatter
    • SQL Injection Attack
    • Aggregate Functions in SQL
    • SQL REVOKE
    • SQL Select Distinct Count
    • IF ELSE Statement in SQL
    • SQL CASE Statement
    • SQL While Loop
    • SQL BIGINT
    • SQL Crosstab
    • SQL Wildcard Character
    • SQLAlchemy Filter
    • SQLAlchemy SQLite
    • SQLAlchemy DateTime
    • SQLAlchemy create_engine
    • SQL INSTR()
    • SQL now
    • SQL synonyms
    • SQLite?export to csv
    • What is Procedure in SQL
    • Stored Procedure in SQL?
    • SQL Server Constraints
    • SQL DELETE ROW
    • Column in SQL
    • Table in SQL
    • SQL Virtual Table
    • SQL Merge Two Tables
    • SQL Table Partitioning
    • SQL Temporary Table
    • SQL Clone Table
    • SQL Rename Table
    • SQL LOCK TABLE
    • SQL Clear Table
    • SQL DESCRIBE TABLE
    • SQL Mapping
    • Cursors in SQL
    • AND in SQL
    • Wildcard in SQL
    • SQL FETCH NEXT
    • SQL Views
    • SQL Delete View
    • Triggers in SQL
    • SQL UPDATE Trigger
    • SQL AFTER UPDATE Trigger
    • SQL Update Statement
    • SQL DROP TRIGGER
    • Types of SQL Views
    • SQL Port
    • SQL Clustered Index
    • SQL COMMIT
    • Distinct Keyword in SQL
    • PARTITION BY in SQL
    • SQL Set Operators
    • SQL UNION ALL
    • Metadata in SQL
    • SQL Bulk Insert
    • Array in SQL
    • SQL REGEXP
    • JSON in SQL
    • SQL For loop
    • EXPLAIN in SQL
    • ROLLUP in SQL
    • Escape Character SQL
    • SQL Cluster
    • SQL Backup
    • SQL Pattern Matching
    • SQL Users
    • ISNULL SQL Server
    • SQL pivot
    • SQL Import CSV
    • SQL if then else
    • SQL ignore-case
    • SQL Matches
    • SQL Search String
    • SQL Column Alias
    • SQL extensions
    • SQL Substring Function
    • Charindex SQL
  • NoSQ
    • NoSQL Databases List
    • NoSQL Injection
    • NoSQL vs SQL Databases
  • Interview Questions
    • SQL Interview Questions
    • Advance SQL Interview Questions
    • SQL Joins Interview Questions
    • SQL Server Interview Questions

Related Courses

JDBC Training Course

PHP course

Windows 10 Training

SQL Course Training

PL/SQL Certification Courses

Oracle Certification Courses

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - SQL Training Program (7 Courses, 8+ Projects) Learn More