MySQL Joins

MySQL Joins are queries that combine the data of multiple tables based on their common columns and constraints to produce a combined result set.

The following joins supported by MySQL

1. Inner join
2. Left join
3. Right join
4. Cross join
5. Full Join

MySQL Inner Join

The inner join is also called a natural join. Inner join adds the records to the resultset only if both the tables have the record with the matching condition. In other words, it checks for equality.

We can do an inner join using the keywords INNER JOIN in the from clause of a query. Whenever we have a query where we check the equality of two columns, we are essentially doing an Inner join.

MySQL Inner Join syntax

The Basic Syntax of an Inner join is,

Select <column-names> FROM <table1> INNER JOIN <table2> ON <equality condition>

Example:

SELECT C.CATEGORY_ID,C.NAME,FC.FILM_ID,FC.LAST_UPDATE
FROM CATEGORY C
INNER JOIN FILM_CATEGORY FC
ON C.CATEGORY_ID = FC.CATEGORY_ID;

This is equivalent to saying

SELECT C.CATEGORY_ID,C.NAME,FC.FILM_ID,FC.LAST_UPDATE
FROM CATEGORY C, FILM_CATEGORY FC
WHERE C.CATEGORY_ID = FC.CATEGORY_ID;

Both queries produce the same result which is:

OR if the column name is the same, we can use

Select <column-names> FROM <table1> INNER JOIN <table2> USING <column-name>

For example:

SELECT C.CATEGORY_ID,C.NAME,FC.FILM_ID,FC.LAST_UPDATE
FROM CATEGORY C
INNER JOIN FILM_CATEGORY FC
USING (CATEGORY_ID);

MySQL Left Join

In MySQL, the Left join and the left outer join are the same. The Left join essentially retains all the data from the left(first) table and the common values from the second table. The values that persist only in the second table are not included.

Consider  table1 LEFT [OUTER] JOIN  table2.

In this case, the result Set will have :: All records that are unique to table1 + all the records common between table1 and table2

SELECT C.CUSTOMER_ID,C.FIRST_NAME,C.LAST_NAME
FROM CUSTOMER C
LEFT JOIN ACTOR A
ON C.LAST_NAME = A.LAST_NAME
WHERE A.LAST_NAME IS NULL
ORDER BY C.LAST_NAME DESC;

MySQL Right Join

Right join and right outer joins are the same in mysql. The right join considers the second table. All the data from the second table including the rows which are common with the second table are included in the output. The data that is present only in the first table is excluded.

We will consider the query from the left join example just with a right join here. Observe the difference in the output

SELECT C.CATEGORY_ID,C.NAME,FC.FILM_ID,FC.LAST_UPDATE
FROM CATEGORY C
RIGHT JOIN FILM_CATEGORY FC
USING (CATEGORY_ID);

MySQL Cross Join

A cross join is simply the cartesian product of two tables without any conditions. IT multiples each record from one table with the other. Observe the result of the query below. The Cross join is not the same as the Full Outer join. MySQL does not support a FULL Outer join.

SELECT C.CATEGORY_ID,C.NAME,FC.FILM_ID,FC.LAST_UPDATE
FROM CATEGORY C
CROSS JOIN FILM_CATEGORY FC;

MySQL FULL JOIN

MySQL does not support FULL JOINs. The FULL JOIN can be emulated using the UNION ALL operator we have seen earlier.

MySQL Self Join

When a table is joined to itself using either an inner join or a left join or a right join, it is called a Self-join. A self-join is very useful when there is a relationship between the data of the same table or there is hierarchical data.

Example of Self Join using INNER JOIN:

A Self join using an inner join is as follows:

SELECT a.first_name, a.last_name, b.email
FROM customer a
INNER JOIN customer b
ON a.last_name = b.first_name;

Example of self Join using Left Join

To use self join using the Left join, we do 

SELECT a.first_name, a.last_name, b.email
FROM customer a
LEFT JOIN customer b
ON a.last_name = b.first_name
ORDER BY a.customer_id;

Example of Self join using Right join:

To do a Self-join using the right join, we do:

SELECT a.first_name, a.last_name, b.email
FROM customer a
RIGHT JOIN customer b
ON a.last_name = b.first_name;