What are the different types of JOINS in SQL and MYSQL?

Answer 1

Let me answer this at theoretical level only and lot of material can be found on the net about this -

Following is the type of JOINS -

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

JOIN or INNER 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 INNER, OUTER, 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 :
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 :

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 NULL values.

RIGHT OUTER JOIN or RIGHT JOIN
This 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 NULL values.

FULL OUTER JOIN or FULL JOIN
This join combines 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.

NATURAL JOIN :
It is based on the two conditions : the JOIN is made on all the columns with the same name for equality.
- Removes duplicate columns from the result.
- This seems to be more of theoretical in nature and as a result (probably) most DBMS don't even bother supporting this.

CROSS JOIN :
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).

SELF JOIN :
It is not a different form of JOIN, rather it is a JOIN (INNER, OUTER, etc) of a table to itself.

Answer 2

INNER JOIN (or just JOIN)

The most frequently used clause is INNER JOIN. This produces a set of records which match in both the user and course tables, i.e. all users who are enrolled on a course:

SELECT user.name, course.name
FROM `user`
INNER JOIN `course` on user.course = course.id;

Result:

user.name   course.name
Alice   HTML5
Bob HTML5
Carline CSS3
David   MySQL

LEFT JOIN

What if we require a list of all students and their courses even if they’re not enrolled on one? A LEFT JOIN produces a set of records which matches every entry in the left table (user) regardless of any matching entry in the right table (course):

SELECT user.name, course.name
FROM `user`
LEFT JOIN `course` on user.course = course.id;

Result:

user.name   course.name
Alice   HTML5
Bob HTML5
Carline CSS3
David   MySQL
Emma    (NULL)
RIGHT JOIN

RIGHT JOIN
Perhaps we require a list all courses and students even if no one has been enrolled? A RIGHT JOIN produces a set of records which matches every entry in the right table (course) regardless of any matching entry in the left table (user):

SELECT user.name, course.name
FROM `user`
RIGHT JOIN `course` on user.course = course.id;

Result:

user.name   course.name
Alice   HTML5
Bob HTML5
Carline  CSS3
(NULL)    JavaScript
(NULL)     PHP

RIGHT JOINs are rarely used since you can express the same result using a LEFT JOIN. This can be more efficient and quicker for the database to parse:

SELECT user.name, course.name
FROM `course`
LEFT JOIN `user` on user.course = course.id;

We could, for example, count the number of students enrolled on each course:

SELECT course.name, COUNT(user.name)
FROM `course`
LEFT JOIN `user` ON user.course = course.id
GROUP BY course.id;

Result:

course.name count()
HTML5   2
CSS3    1
JavaScript  0
PHP 0
MySQL   1

OUTER JOIN (or FULL OUTER JOIN)

Our last option is the OUTER JOIN which returns all records in both tables regardless of any match. Where no match exists, the missing side will contain NULL.

OUTER JOIN is less useful than INNER, LEFT or RIGHT and it’s not implemented in MySQL. However, you can work around this restriction using the UNION of a LEFT and RIGHT JOIN, e.g.

SELECT user.name, course.name
FROM `user`
LEFT JOIN `course` on user.course = course.id

UNION

SELECT user.name, course.name
FROM `user`
RIGHT JOIN `course` on user.course = course.id;

Result:

user.name   course.name
Alice   HTML5
Bob HTML5
Carline CSS3
David   MySQL
Emma    (NULL)
(NULL)  JavaScript
(NULL)  PHP

I hope that gives you a better understanding of JOINs and helps you write more efficient SQL queries.