Updated on December 4, 2024

JOINs

Estimated reading: 2 minutes 14 views

1. What are INNER, LEFT, RIGHT, and FULL joins? Provide examples for each.

  • INNER JOIN: Returns only rows with matching values in both tables.
    Example:
				
					SELECT e.name, d.department_name 
FROM employees e 
INNER JOIN departments d 
ON e.department_id = d.id;

LEFT JOIN: Returns all rows from the left table and matched rows from the right table. Unmatched rows in the right table are NULL.
Example:

				
					SELECT e.name, d.department_name 
FROM employees e 
LEFT JOIN departments d 
ON e.department_id = d.id;

RIGHT JOIN: Returns all rows from the right table and matched rows from the left table. Unmatched rows in the left table are NULL.
Example:

				
					SELECT e.name, d.department_name 
FROM employees e 
RIGHT JOIN departments d 
ON e.department_id = d.id;

FULL JOIN: Combines LEFT and RIGHT JOIN, returning all rows from both tables, with unmatched rows filled as NULL.
Example:

				
					SELECT e.name, d.department_name 
FROM employees e 
FULL OUTER JOIN departments d 
ON e.department_id = d.id;

2. Write a query to display all employees and their department names using a join.

To display employees and their department names, use an INNER JOIN if only matched records are needed or a LEFT JOIN if all employees should be included even if they lack a department.
Example:

				
					SELECT e.name AS employee_name, d.department_name 
FROM employees e 
LEFT JOIN departments d 
ON e.department_id = d.id;

3. How do you retrieve records from two tables that have no matches (e.g., “orphans”)?

To find records with no matches, use an OUTER JOIN combined with a WHERE clause checking for NULL values in the join condition.
Example (Find employees without a department):

				
					SELECT e.name 
FROM employees e 
LEFT JOIN departments d 
ON e.department_id = d.id 
WHERE d.id IS NULL;

4. What is a self-join, and when would you use it?

A self-join is when a table is joined with itself. It is used to compare rows within the same table, such as finding employees who report to the same manager or finding hierarchical relationships.
Example (Find employees who share the same manager):

				
					SELECT e1.name AS employee, e2.name AS manager 
FROM employees e1 
JOIN employees e2 
ON e1.manager_id = e2.id;

Leave a Comment

Share this Doc

JOINs

Or copy link

CONTENTS