Database [3i Infotech Placement]: Sample Questions 71 - 71 of 294

Glide to success with Doorsteptutor material for competitive exams : get questions, notes, tests, video lectures and more- for all subjects of your exam.

Question 71

Describe in Detail Essay▾

What are the different types of JOIN operations?

Edit

Explanation

Below the types of joins

  • Inner Join
  • Outer join
    • Left outer join
    • Right outer join
    • Full join
  • Inner join

The INNER JOIN keyword selects records that have matching values in both tables.

Syntax of Inner join

SELECT column_name (s)

FROM table1

INNER JOIN table2 ON table1. column_name = table2. column_name;

  • Outer Join

Left Outer join:

The Left Outer Join
  • The left outer join keyword returns all records from the left table (table1) , and the matched records from the right table (table2) .
  • The result is NULL from the right side, if there is no match.

    Syntax of left outer join

    SELECT column_name (s)

    FROM table1

    LEFT JOIN table2 ON table1. column_name = table2. column_name;

Right Outer join:

The Right Outer Join
  • The right outer join keyword returns all records from the right table (table2) , and the matched records from the left table (table1) .
  • The result is NULL from the left side, when there is no match.

    Syntax of right outer join:

    SELECT column_name (s)

    FROM table1

    RIGHT JOIN table2 ON table1. column_name = table2. column_name;

Full Outer join:

The Full Outer Join
  • The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.

    Full outer join syntax:

    SELECT column_name (s)

    FROM table1

    FULL OUTER JOIN table2 ON table1. column_name = table2. column_name;

  • Simple outer join is combination of left and right outerjoins.
  • Apart from these there are
  • Natural join: Cartisian product
  • Equi join: Which includes = operator in condition
  • NonEqui join: All conditional joins which do not use = in there conditions.