I have been going through many threads on SO and some other forums. So I thought I would summarize "What is SQL JOIN?" and "What are different types of SQL JOINs?".
SQL JOIN is a method to retrieve data from two or more database tables.
SQL JOINs ?
There are a total of five
JOINs. They are :
1. JOIN or INNER JOIN 2. OUTER JOIN 2.1 LEFT OUTER JOIN or LEFT JOIN 2.2 RIGHT OUTER JOIN or RIGHT JOIN 2.3 FULL OUTER JOIN or FULL JOIN 3. NATURAL JOIN 4. CROSS JOIN 5. SELF JOIN
In this kind of a
JOIN, we get all records that match the condition in both the tables,
and records in both the tables that do not match are not reported.
In other words,
INNER JOIN is based on the single fact that : ONLY the matching entries in BOTH the tables SHOULD be listed.
Note that a
JOIN without any other
JOIN keywords (like
LEFT, etc) is an
INNER JOIN. In other words,
INNER JOIN is
a Syntactic sugar for
JOIN (see : Difference between JOIN and INNER JOIN).
OUTER JOIN retrieves
Either, the matched rows from one table and all rows in the other table Or, all rows in all tables (it doesn't matter whether or not there is a match).
There are three kinds of Outer Join :
2.1 LEFT OUTER JOIN or LEFT JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the
right table. If there are no columns matching in the right table, it returns
2.2 RIGHT OUTER JOIN or RIGHT JOIN
JOIN returns all the rows from the right table in conjunction with the matching rows from the
left table. If there are no columns matching in the left table, it returns
2.3 FULL OUTER JOIN or FULL JOIN
LEFT OUTER JOIN and
RIGHT OUTER JOIN. It returns row from either table when the
conditions are met and returns
NULL value when there is no match.
In other words,
OUTER JOIN is based on the fact that : ONLY the matching entries in ONE OF the tables (RIGHT or LEFT)
or BOTH of the tables(FULL) SHOULD be listed.
Note that `OUTER JOIN` is a loosened form of `INNER JOIN`.
It is based on the two conditions :
JOINis made on all the columns with the same name for equality.
This seems to be more of theoretical in nature and as a result (probably) most DBMS don't even bother supporting this.
It is the Cartesian product of the two tables involved. The result of a
CROSS JOIN will not make sense
in most of the situations. Moreover, we wont need this at all (or needs the least, to be precise).
It is not a different form of
JOIN, rather it is a
OUTER, etc) of a table to itself.
Depending on the operator used for a
JOIN clause, there can be two types of
JOINs. They are
JOIN type (
OUTER, etc), if we use ONLY the equality operator (=), then we say that
JOIN is an
This is same as
EQUI JOIN but it allows all other operators like >, <, >= etc.
Many consider both
EQUI JOINand Theta
JOINs. But I strongly believe that its a mistake and makes the ideas vague. Because
OUTER JOINetc are all connected with the tables and their data where as
THETA JOINare only connected with the operators we use in the former.
Again, there are many who consider
NATURAL JOINas some sort of "peculiar"
EQUI JOIN. In fact, it is true, because of the first condition I mentioned for
NATURAL JOIN. However, we dont have to restrict that simply to
NATURAL JOINs alone.
OUTER JOINs etc could be an