In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.

INNER JOIN should be used when you only want to pull data that exists in both of the tables.

OUTER JOIN should be used when you want to pull all of the matching data in the first table.

CROSS JOIN returns the Cartesian product of the sets of records from the two or more joined tables.

LEFT JOIN keyword returns all rows from the left table 1, with the matching rows in the right table 2.


More Than one Table. The query uses an INNER JOIN to combine the tables.

SELECT pet.name,
(YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
remark
FROM pet INNER JOIN event
ON pet.name = event.name
WHERE event.type = 'litter';

Sometimes it is useful to join a table to itself, if you want to compare records in a table to other records in that same table.

SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
FROM pet AS p1 INNER JOIN pet AS p2
ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';

In this query, we specify aliases for the table name to refer to the columns and keep straight which instance of the table each column reference is associated with.