MySQL Joins Tutorial (Left, Right, Inner, Nature, self)

2012-06-22T15:00:00.000-07:00

Today we are going to discuss MySQL Joins (Left Join, Right Join, Inner Join, Self Join and Natural Join) in additional to these, we will touch the Straight Join (STRAIGHT_JOIN)

Let's we begin from joins syntax. The syntax is as follows:

SELECT 
   t1.col1, 
   t2.col2
FROM
   table1 AS t1
   JOIN 
      table2 AS t2
         ON (t1.col1 = t2.col2)
WHERE t1.col1=t2.col2

SELECT as usual, then going FROM table JOIN, join another table to the first table and then ON. join ON (condition) after maybe used WHERE clause, but not necessary.
In total ON is equal to WHERE and is not limited to one condition, then you can append GROUP, HAVING, LIMIT, etc.

Now let's discuss the joins type, they are few and we will talk about each of them.
MySQL's INNER JOIN / JOIN
INNER JOIN is equal to JOIN. next 2 statements are equal by their meaning:

SELECT 
   t1.col1, t1.col2
FROM
   table1
   INNER JOIN table2
   ON (t1.col1 = t2.col2)

# previous query is equal to next
SELECT 
   t1.col1, t1.col2
FROM
   table1
   JOIN table2
   ON (t1.col1 = t2.col2)

INNER JOIN return rows when there is at least one match in both tables, otherwise the query wouldn't return results.

MySQL's LEFT JOIN
LEFT JOIN return ALL rows from the left table even if no match found on the right table. example:

SELECT 
   t1.col1, t1.col2
FROM
   table1 # returns all rows from table 1
   LEFT JOIN table2 # even f no match in table 2
   ON (t1.col1 = t2.col2)

MySQL's RIGHT JOIN
RIGHT JOIN is a reversed join of left, returns all rows from right table even if no match found in left. refer to previous example of left join.

NATURAL JOIN
NATURAL JOIN acts as INNER JOIN, the difference is that INNER JOIN may return any of the columns while NATURAL JOIN will return only these with same name in both tables.
NATURAL JOIN also doesn't apply ON clause.

SELF JOIN
SELF JOIN is a kind of JOIN, SELF JOIN can be any of these, INNER, LEFT or RIGHT.
SELF JOIN joins same table how many times you required. example:

SELECT
   *
FROM
   table1 AS t1
   JOIN table1 AS t2
      ON (t1.col1<>t2.col1)

In this example we joined same table, this is kind of implementation of SELF JOIN.

STAIGHT_JOIN
STRAIGHT_JOIN is a keyword that tells (forces) MySQL's Optimizer to join tables in order they are type in the query, this have it's benefits and disadvantages.
This kind of join may be seen very useful however no one will advice to use them always.
Use EXPLAIN/EXPLAIN EXTENDED to understand whether to use it or not.

SELECT
   *
FROM
   table1 AS t1
   STRAIGHT_JOIN table1 AS t2
      ON (t1.col1<>t2.col1)

In this example, no matter what will happen, optimizer will read t1 and then will read table2 in an inner loop.

Have fun playing these things ;-)
Sincerely,
VR.

MySQL Tunning & Performance Tweaks by Ruskevych Valentin

MySQL Joins Tutorial (Left, Right, Inner, Nature, self)