What is Inner join and Left Outer Join

Upasana | May 24, 2019 | 2 min read | 196 views


What is Inner Join?

INNER JOIN is the most common and the default join operation. This join creates a resultset by combining the column values of two tables (L and R) based upon the predicate. Each row of L (left table) is compared with each row of R (right table) to find all pairs of rows that satisfy the join predicates. When the join-predicate is satisfied, column values for each matched pair of rows of L and R are combined into a result row. Example query is shown below.

inner join
Red colored area in diagram reflects the result of INNER JOIN on two Tables L & R
Explicit Join Notation
SELECT
    *
FROM
    employee E
        INNER JOIN
    department D ON E.DepartmentID = D.DepartmentID;
Implicit Join Notation
 SELECT
    *
FROM
    employee E,
    department D
WHERE
    E.DepartmentID = D.DepartmentID;

Example usecase

SQL - Find Customers who have placed at least one order
SELECT
    c.name, c.contact, c.email
FROM
    Customer c
        INNER JOIN
    Orders o ON c.id = o.c_id;

What is LEFT OUTER JOIN?

An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists.

left outer join

LEFT OUTER JOIN is the result of a left outer join (or simply left join) for table L and R always contains all records of the "left" table (L), even if the join-condition does not find any matching record in the "right" table ®. This means that if the ON clause matches 0 (zero) records in R (for a given record in L), the join will still return a row in the result (for that record)—but with NULL in each column from R. A left outer join returns all the values from an inner join plus all values in the left table that do not match to the right table.

Left Outer Join SQL
SELECT
    *
FROM
    TableL L
        LEFT OUTER JOIN
    TableR R ON L.key = R.key;

Another variation of LEFT OUTER JOIN where Right Table Key is null.

left outer join 2
SQL Query for LEFT OUTER JOIN
SELECT
    *
FROM
    TableL L
        LEFT OUTER JOIN
    TableR R ON L.key = R.key
WHERE
    R.key IS NULL;

Top articles in this category:
  1. SQL - Write a query to find customers who have no orders yet
  2. RBS Java Programming Interview Questions
  3. Citibank Java developer interview questions
  4. Markit Java Interview Questions
  5. Morgan Stanley Java Interview Questions
  6. Cracking core java interviews - question bank
  7. Multi-threading Java Interview Questions for Investment Bank

Recommended books for interview preparation:

Find more on this topic: