What is Inner join and Left Outer Join

Carvia Tech | May 24, 2019 | 2 min read | 0 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. RBS Java Programming Interview Questions
  2. Citibank Java developer interview questions
  3. Cracking core java interviews - question bank
  4. Top 30 Hibernate and Spring Data JPA interview questions
  5. Top 50 Multi-threading Java Interview Questions for Investment Bank
  6. Morgan Stanley Investment Banking Java Interview Questions
  7. Top 20 Java Concurrency Interview Questions and Answers



Find more on this topic:
Java Interviews image
Java Interviews

Interview - Product Companies, eCommerce Companies, Investment Banking, Healthcare Industry, Service Companies and Startups.

Last updated 1 week ago


Recommended books for interview preparation:

This website uses cookies to ensure you get the best experience on our website. more info