In real life situation you often need to query two or more tables at time and bring a combined result set. This is technically referred to as a join, since it involves joining different tables, based on a common field.
Inner join: The INNER JOIN is the most common type of join. A join that returns only those rows that have a match in both joined tables.
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name FROM employees AS t1 INNER JOIN departments AS t2 ON t1.dept_id = t2.dept_id ORDER BY emp_id;
Outer join: An outer join returns the rows even if they don't have related rows in the joined table. There are three types of outer joins: left outer join (or left join), right outer join (or right join), and full outer join (or full join).
A LEFT JOIN statement returns all rows from the left table along with the rows from the right table for which the join condition is met.
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name FROM employees AS t1 LEFT JOIN departments AS t2 ON t1.dept_id = t2.dept_id ORDER BY emp_id;
The RIGHT JOIN is the exact opposite of the LEFT JOIN. It returns all rows from the right table along with the rows from the left table for which the join condition is met.
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name FROM employees AS t1 RIGHT JOIN departments AS t2 ON t1.dept_id = t2.dept_id ORDER BY dept_name;
A FULL JOIN returns all rows from the joined tables, whether they are matched or not i.e. full join combines the functions of a LEFT JOIN and a RIGHT JOIN.
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name FROM employees AS t1 FULL JOIN departments AS t2 ON t1.dept_id = t2.dept_id ORDER BY emp_name;
If your Database does not support FULL JOIN (MySQL does not support FULL JOIN), then you can use UNION ALL clause to combine these two JOINS:
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name FROM employees AS t1 LEFT JOIN departments AS t2 ON t1.dept_id = t2.dept_id UNION ALL SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name FROM employees AS t1 RIGHT JOIN departments AS t2 ON t1.dept_id = t2.dept_id
Cross join: Each row of one table is combined with each row of another table. This type of result set is called a Cartesian product or cross product.
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name FROM employees AS t1 CROSS JOIN departments AS t2;
Tip: A cross join creates a Cartesian product or multiplication of all rows in one table with all rows in another. So, for example, if one table has 5 rows and another has 10 rows, a cross-join query produces 50 rows.