How does SQL JOIN clause works: Part –I (a little bit theoretical?)

A SQL JOIN clause is important issues in database operation. Actually one of my friend asked me – what are the different types of joining and how those joining are working. And what is the basic difference between them? Well in our daily life 😀 we are using different types of database like DB2, MS SQL Server, Oracle, mysql etc. I like mysql because it is an open source database. But the entire database use same SQL command. SQL – structure query language; it is a slandered query language for all database. Currently I am using mysql for all of my development that’s why all I am talking about in the context of mysql.

A SQL JOIN clause works on more than one table. In our practical life we used 3rd normal relational database(RDBMS). That’s why only one table can’t represent any meaning full data or entity. So JOIN clause combines record from two or more relational table and create a new temporary table that is “join table”.
Well in our daily life (those who are live with development 🙂 ) we are using two types of JOIN clause.

1. INNER JOIN
2. OUTER JOIN

INNER JOIN:
An inner join essentially combines the records from two tables (A and B) based on a given join-predicate. The SQL-engine computes the Cartesian product of all records in the tables. Thus, processing combines each record in table A with every record in table B. Only those records in the joined table that satisfy the join predicate remain. This type of join occurs the most commonly in applications, and represents the default join-type.
And also there are two different syntactical ways to express joins. One is “explicit join notation” where we used JOIN keywords and another is “implicit join notation”.
Example of an explicit inner join:

SELECT *
FROM table_1
INNER JOIN table_2
ON table_1.field = table_2.field

Example of an implicit inner join:

SELECT *
FROM table_1, table_2
WHERE table_1.field = table_2.field

There are three types of inner joins

Equi-join
An equi-join is a specific type of comparator-based join, or theta join, uses only equality comparisons in the join-predicate.

SELECT *
FROM table_1
INNER JOIN table_2
ON table_1.feild_1 = table_2.feild_1

The resulting joined table contains two columns named field_1, one from table table_1 and one from table table_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.

SELECT *
FROM table_1 NATURAL JOIN table_2

Using the NATURAL JOIN keyword to express joins can suffer from ambiguity at best, and leaves systems open to problems if schema changes occur in the database. For example, the removal, addition, or renaming of columns changes the semantics of a natural join. Thus, the safer approach involves explicitly coding the join-condition using a regular inner join.

Cross join
A cross join or 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.
If A and B are two sets, then cross join = A X B.
Example of an explicit cross join:

SELECT *
FROM table_1 CROSS JOIN table_2

Example of an implicit cross join:

SELECT *
FROM table_1, table_2;

The cross join does not apply any predicate to filter records from the joined table. some times we can further filter the results of a cross join by using a WHERE clause.

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).
(For a table to qualify as left or right its name has to appear after the FROM or JOIN keyword, respectively.)

Left outer join
The result of a left outer join for tables A and B always contains all records of the “left” table (A), even if the join-condition does not find any matching record in the “right” table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).
Example of a left outer join:

SELECT *
FROM table_1
LEFT OUTER JOIN table_2
ON table_1.feild_1 = table_2.feild_1

Right outer join
A right outer join closely resembles a left outer join, except with the tables reversed. Every record from the “right” table (B) will appear in the joined table at least once. If no matching row from the “left” table (A) exists, NULL will appear in columns from A for those records that have no match in A.
Example right outer join:

SELECT *
FROM table_1
RIGHT OUTER JOIN table_2
ON table_1.feild_1 = table_2.feild_1

Full outer join
A full outer join 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.
Example full outer join:

SELECT *
FROM table_1
FULL OUTER JOIN table_2
ON table_1.field_1= table_2.field_1

mysql database systems do not support this functionality directly, but they can emulate it through the use of left and right outer joins and unions. The same example can appear as follows:

SELECT *
FROM table_1
LEFT JOIN table_2
ON table_1.field_1 = table_2.field_1
UNION
SELECT *
FROM table_1
RIGHT JOIN table_2
ON table_1.field_1 = table_2.field_1
WHERE table_1.field_1 IS NULL

or as follows:

SELECT *
FROM table_1
LEFT JOIN table_2
ON table_1.field_1 = table_2.field_1
UNION
SELECT *
FROM table_2
LEFT JOIN table_1
ON table_1.field_1 = table_2.field_1
WHERE table_1.field_1 IS NULL

or as follows:

SELECT *
FROM table_2
RIGHT JOIN table_1
ON table_1.field_1 = table_2.field_1
UNION
SELECT *
FROM table_1
RIGHT JOIN table_2
ON table_1.field_1 = table_2.field_1
WHERE table_1.field_1 IS NULL

there are some algorithm for implement this JOIN clause. you could study more on this topic. and if you want to study about SQL JOIN clause then there are lots of site. but i can give you some they are really help full to understand how the SQL JOIN clause working specially for newbie 😀 .

• w3schools
• mysql official site
• a tutorial site

hopefully all the above information will help you to understand SQL JOIN clause 🙂 and i am also hope full to manage my times and post Part – II (the total practical of mysql JOIN) so see you soon here 😀