Question 32

Explanation

Correct options:

1. An inner join returns matched rows

• True

• An INNER JOIN returns rows only when there is a match between the join columns in both tables.

2. A left or right outer join returns matched and unmatched rows

• True

• A LEFT OUTER JOIN returns:

  • All matched rows + all unmatched rows from the left table

• A RIGHT OUTER JOIN returns:

  • All matched rows + all unmatched rows from the right table

3. A full outer join returns matched and unmatched rows

• True

• A FULL OUTER JOIN returns:

  • All matched rows

  • All unmatched rows from both tables

4. Outer joins can be used when there are multiple join conditions on two tables

• True

• You can apply multiple join conditions using OUTER JOINS, just like with INNER JOINS.

Incorrect options:

• A full outer join must use Oracle syntax

  • False

  • Oracle supports ANSI SQL syntax, which is recommended:

    SELECT * FROM A FULL OUTER JOIN B ON A.id = B.id;

  • The Oracle (+) syntax does not support FULL OUTER JOINs.

  • So ANSI syntax is required, but not exclusive to Oracle — other databases use it too.

• Outer joins can only be used between two queries

  • False

  • OUTER JOINS are used to combine tables, not queries.

  • You can also perform joins between more than two tables.

Oracle JOINS are used to retrieve data from multiple tables. An Oracle JOIN is performed whenever two or more tables are joined in a SQL statement.

There are 4 different types of Oracle joins:

1. Oracle INNER JOIN (or sometimes called simple join)

2. Oracle LEFT OUTER JOIN (or sometimes called LEFT JOIN)

3. Oracle RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)

4. Oracle FULL OUTER JOIN (or sometimes called FULL JOIN)

INNER JOIN (simple join)

Chances are, you've already written a statement that uses an Oracle INNER JOIN. It is the most common type of join. Oracle INNER JOIN return all rows from multiple tables where the join condition is met.

Syntax

The syntax for the INNER JOIN in Oracle/PLSQL is:

SELECT columns
FROM table1 
INNER JOIN table2
ON table1.column = table2.column;

Visual Illustration

In this visual diagram, the Oracle INNER JOIN returns the shaded area:

The Oracle INNER JOIN would return the records where table1 and table2 intersect.

√  An inner join returns matched rows

LEFT OUTER JOIN

Another type of join is called an Oracle LEFT OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the Oracle LEFT OUTER JOIN is:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the LEFT OUTER JOIN keywords are replaced with LEFT JOIN.

Visual Illustration

In this visual diagram, the Oracle LEFT OUTER JOIN returns the shaded area:

The Oracle LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1.

RIGHT OUTER JOIN

Another type of join is called an Oracle RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the Oracle RIGHT OUTER JOIN is:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the RIGHT OUTER JOIN keywords are replaced with RIGHT JOIN.

Visual Illustration

In this visual diagram, the Oracle RIGHT OUTER JOIN returns the shaded area:

The Oracle RIGHT OUTER JOIN would return the all records from table2 and only those records from table1 that intersect with table2.

With a RIGHT OUTER JOIN, all the records of table2, whether they match table1 or not, some of the records in table2 may match records in table1 but matches are not necessary for successful execution.

√  A left or right outer join returns matched and unmatched rows

FULL OUTER JOIN

Another type of join is called an Oracle FULL OUTER JOIN. This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.

Syntax

The syntax for the Oracle FULL OUTER JOIN is:

SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the FULL OUTER JOIN keywords are replaced with FULL JOIN.

Visual Illustration

In this visual diagram, the Oracle FULL OUTER JOIN returns the shaded area:

The Oracle FULL OUTER JOIN would return the all records from both table1 and table2.

√  A full outer join returns matched and unmatched rows