JOIN clause in SQL

Referenced Links

A JOIN clause in SQL combines records from two tables in a relational database and results in a new (temporary) table, also called a "joined table". SQL specifies two types of joins: inner and outer.

A predicate, i.e. a condition used to join two tables, is used to create a join between two tables.
SQL:2003 specifies two different syntactical ways to express joins.
The ' EXPLICIT JOIN notation' uses the keyword join.
The 'IMPLICITJOIN notation' lists the tables for joining in the FROM clause of a SELECT statement, using commas to separate them.

A> INNER JOIN : Represents the default join type, and occurs most commonly in applications.
It returns the all the records from the tables which satisfy the predicate.
Inner joins are fully commutative. “A inner join B” and “B inner join A” are equivalent.
One can further classify inner joins as equi-joins, as natural joins, or as cross-joins.
eg : Explicit Inner join :

SELECT
table1.*, table2.*
FROM table1
INNER JOIN table2
ON ( table2.table1ID = table1.ID ) -- predicate
-- Here table2.table1ID refers to (FK) table1.ID column.

Thus this query would return all the rows in table 2 whose table1ID matches the values of table1.ID, alongwith the corresponding table1 rows.

Implicit Inner Join:

SELECT table1.*, table2.*
FROM table1, table2
where ( table2.table1ID = table1.ID ) -- predicate

This query would return the same results as the one above.


Types Of Inner JOIN :
1> EQUI-JOIN [equijoin, theta join] :
is specific type of comparator-based join which uses only equality based join-predicates.
eg :
SELECT
table1.*, table2.*
FROM table1
INNER JOIN table2
ON ( table2.table1ID = table1.ID ) -- predicate
-- Here only the equality operator is used.


2) Natural join :
A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables. The resulting joined table contains only one column for each pair of equally-named columns.

eg: consider both tables have only one column with same name say com_col , then.

SELECT *
FROM table1
NATURAL JOIN table2

will return the following structure:

table1.cols, com_col , table2.cols

3) CROSS JOIN [cartesian join] : Provides the foundation upon which all types of inner joins operate. A cross join returns the cartesian product of the sets of records from the two joined tables. Thus it equates to an inner join where the join-condition always evaluates to True. i.e. it returns all the rows in one table for every row in the another.
eg:
SELECT * FROM table1 CROSS JOIN table2 -- Explicit

SELECT * FROM table1,table2

Both the queries would return rows equal to the product of rows in both tables.





B> OUTER JOIN :

An outer join does not require each record in the two joined tables to have a matching record in the other table. The joined table retains each record — even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).

Full outer joins are commutative. In addition, “A left outer join B” and “B right outer join A” are equivalent.

1> LEFT OUTER JOIN :
Its result contains all the rows satisfying the predicate and also all the remaining rows from the left table for which there are no matching records in the right table

eg:
SELECT
table1.*, table2.*
FROM table1
LEFT OUTER JOIN table2
ON ( table2.table1ID = table1.ID )
-- Here table2.table1ID refers to (FK) table1.ID column.

This query would return all the rows in table1 but only matching rows from table2. For all the rows in table1, that do not have corresponding rows in table2, the values for table2 columns will be NULL.

2> RIGHT OUTER JOIN :
This is similar to the LEFT OUTER JOIN, except that it will return all the rows from the table on right, but only the rows from left table which have matching values in the right table.

eg:
SELECT
table1.*, table2.*
FROM table1
RIGHT OUTER JOIN table2
ON ( table2.table1ID = table1.ID )
-- Here table2.table1ID refers to (FK) table1.ID column.

Now in this case all the rows from table2 two will be returned, whereas from table1 only those rows will be populated which will have matching values in table2.


3> FULL OUTER JOIN :
The result in this case combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

eg:
SELECT
table1.*, table2.*
FROM table1
FULL OUTER JOIN table2
ON ( table2.table1ID = table1.ID )
-- Here table2.table1ID refers to (FK) table1.ID column.

In this case all the rows from table1 as well as from table2 are returned. Also the rows which dont have any matching values in the other table will be populated with null values.
i.e. for the rows from table1 which do not have corresponding values in table2, will have the columns from table2 poulated as NULL.



Referenced Links