JOIN Types

• INNER JOIN: Returns only matching rows between both tables.
• LEFT JOIN: Returns all rows from the left table and matching rows from the right table. If no match is found, NULL values are returned.
• RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
• FULL JOIN: Returns all rows from both tables. If there is no match, NULLs are returned.
• SELF JOIN: A table joins itself to compare rows within the same table.

SELECT students.name,students.clubNumber,clubs.clubName
FROM students
LEFT JOIN clubs
on student.clubNumber = clubs.clubNumber

SELECT 
    employees.name, 
    departments.department_name
FROM employees
CROSS JOIN departments;

Example

SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
INNER JOIN employees e2
ON e1.manager_id = e2.id;