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.
