JOINS Definition Joins in MySQL allow you to retrieve data from two or more tables based on a related column. They are used to combine rows from multiple tables.
Types of Joins 1.
INNER JOIN:
- Returns rows where there is a match in both tables.
- Syntax:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
- Example:
SELECT employees.name, departments.name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;
2.
LEFT JOIN (OUTER JOIN):
- Returns all rows from the left table and matching rows from the right table. Non-matching rows have
NULL.
- Example:
SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;
3.
RIGHT JOIN (OUTER JOIN):
- Returns all rows from the right table and matching rows from the left table. Non-matching rows have
NULL.
- Example:
SELECT employees.name, departments.name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;
4.
FULL OUTER JOIN:
- Returns all rows from both tables, matching where possible. Not natively supported in MySQL, but can be simulated using
UNION.
- Example:
SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id
UNION
SELECT employees.name, departments.name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;
5.
CROSS JOIN:
- Returns the Cartesian product of both tables.
- Example:
SELECT employees.name, departments.name
FROM employees
CROSS JOIN departments;
Interview Questions 1.
What is the difference between INNER JOIN and OUTER JOIN? - INNER JOIN only includes rows with matches in both tables, while OUTER JOIN includes unmatched rows.
2.
How can you simulate a FULL OUTER JOIN in MySQL? - Use
UNION of
LEFT JOIN and
RIGHT JOIN.
3.
What is a Cartesian product, and when does it occur? - A Cartesian product occurs in a
CROSS JOIN or when no
ON condition is specified, resulting in all possible row combinations.
