SQL Joins
 

Oracle SQL 

SQL Introduction    SQL Introduction 

SQL Insert    SQL Insert

SQL Update    SQL Update

SQL Delete    SQL Delete

SQL Joins   SQL Joins 

SQL Subqueries    SQL Subqueries

SQL Views    SQL Views

Oracle PL/SQL

PL/SQL Introduction    PL/SQL Introduction

PL/SQL Cursors    PL/SQL Cursors

PL/SQL Triggers    PL/SQL Triggers

PL/SQL Interview Questions    PL/SQL Questions

SQL Joins

The simplest JOIN is a two-table SELECT that has no WHERE clause qualifiers.

SQL Cartesian product:

The number of rows in the result table is equal to the number of rows in the first source table multiplied by the number of rows in the second source table.

SQL Equijoin:

An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns.

(here is an example from the hr schema)

SELECT l.location_id, city, department_name
FROM locations l, departments
WHERE l.location_id = departments.location_id;

SQL Cross Join:

It is without the WHERE clause

Example 1:

Select * from locations, departments;

Example 2:

Select * from locations CROSS JOIN departments;

SQL Natural Join:

Natural Join is based on all columns with same name in both tables.

You should not qualify the column names with the table name or table alias name.

Example 1:

SELECT location_id, city, department_name
FROM locations NATURAL JOIN departments;

we cannot say ::

SELECT l.location_id, city, department_name
FROM locations l NATURAL JOIN departments;
(even though the LOCATION_ID column is in both tables, we did not qualify this column in the SELECT clause. You cannot qualify the column names when using the NATURAL JOIN clause.)

Example 2: ( For 3 Tables)

SELECT field 1, field 2, field 3
from table1
NATURAL JOIN table2
NATURAL JOIN table3;

Traditional Oracle syntax for above query is:

Select field1, field2, field3
from table1, table2, table3
where table1_id = table2_id
and table2_id = table3_id;

"USING" in SQL Joins:

If you have many columns that have the same names in the tables you are joining and those columns do not have the same datatype, or you want to specify the columns should be considered for an equijoin, you can use the JOINUSING syntax. The USING clause specifies the column names that should be used to join the tables. The column names should not be qualified with a table name or table alias.

Example:

SELECT location_id, city, department_name
from locations JOIN departments USING (location_id);

"ON" condition in SQL Joins

When you do not have common column names between tables to make a join or if you want to specify arbitrary join conditions, you may use the JOIN ON syntax.

SELECT *
from table1 JOIN table2
ON table1.id = table2.id ;

SQL OUTER Joins:

When you join two tables, the first table may have rows that do not match any rows in second table and vice versa. If you perform inner join on those tables, all unmatched rows are excluded from the result. OUTER JOINS don’t exclude unmatched rows.

There are three types of outer joins:

Left Outer join  Left Outer Join

Right Outer join  Right Outer Join

Full Outer join  Full Outer Join

Left Outer join  Left Outer Join:

A left outer join is a join between two tables that returns rows based on the matching condition, as well as unmatched rows from the table to the left of the JOIN clause.

Example

SELECT *
FROM firsttable
LEFT JOIN secondtable
ON firsttable.keyfield = secondtable.foreign_keyfield

Right Outer join  Right Outer Join:

A right outer join is a join between two tables that returns rows based on the matching condition, as well as unmatched rows from the table to the right of the JOIN clause.

Exampe

SELECT *
FROM firsttable
RIGHT JOIN secondtable
ON firsttable.keyfield = secondtable.foreign_keyfield

Full Outer join  Full Outer Join:

The full outer join combines the features of the left outer join and the right outer join. It retains the unmatched rows from both the left and the right tables.

Example

Select last_name, department_name
from employees
full outer join
departments
on
employees.department_id = departments.department_id;

Little more on SQL Joins:

In Oracle8i Left outer join was like this:

select last_name, department_name
from employees , departments
where employees.department_id = departments.department_id(+);

In Oracle9i Left outer join was like this:

Select last_name, department_name
from employees
left outer join
departments
on
employees.department_id = departments.department_id;

In Oracle8i Right Outer Join is like:

Select last_name, department_name
from employees , departments
where employees.department_id(+) = departments.department_id;

In Oracle9i Right Outer Join is like:

Select last_name,department_name
from employees
right outer join
departments
on
employees.department_id = departments.department_id;