The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Joins

Next ►
►

In SQL, joins are used to combine rows from two or more tables based on a related column between them. Joins are fundamental for querying relational databases, as they allow you to retrieve data spread across multiple tables in a single query.

Types of Joins

There are several types of joins in SQL, each serving a different purpose:

  1. Inner Join
  2. Left (Outer) Join
  3. Right (Outer) Join
  4. Full (Outer) Join
  5. Cross Join
  6. Self Join

  1. Inner Join

An inner join returns only the rows that have matching values in both tables.

Syntax:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

Example:

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

Explanation:

  • This query selects the name column from the employees table and the department_name column from the departments table.
  • It joins the two tables on the department_id column in the employees table and the id column in the departments table.

  1. Left (Outer) Join

A left join returns all rows from the left table and the matched rows from the right table. If no match is found, NULL values are returned for columns from the right table.

Syntax:

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

Example:

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

Explanation:

  • This query selects all employees and their corresponding department names.
  • If an employee does not belong to any department, the department_name will be NULL.

  1. Right (Outer) Join

A right join returns all rows from the right table and the matched rows from the left table. If no match is found, NULL values are returned for columns from the left table.

Syntax:

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

Example:

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

Explanation:

  • This query selects all departments and their corresponding employees.
  • If a department has no employees, the name will be NULL.

  1. Full (Outer) Join

A full join returns all rows when there is a match in either left or right table. Rows without a match in one of the tables will have NULL values for columns from that table.

Syntax:

SELECT columns
FROM table1
FULL JOIN table2
ON table1.common_column = table2.common_column;

Example:

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

Explanation:

  • This query selects all employees and departments.
  • If an employee does not belong to any department, the department_name will be NULL.
  • If a department has no employees, the name will be NULL.

  1. Cross Join

A cross join returns the Cartesian product of the two tables, i.e., it returns all possible combinations of rows from the two tables.

Syntax:

SELECT columns
FROM table1
CROSS JOIN table2;

Example:

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

Explanation:

  • This query returns every possible combination of employees and departments.

  1. Self Join

A self join is a regular join but the table is joined with itself.

Syntax:

SELECT a.columns, b.columns
FROM table a, table b
WHERE condition;

Example:

SELECT a.name AS Employee, b.name AS Manager
FROM employees a, employees b
WHERE a.manager_id = b.id;

Explanation:

  • This query selects employees and their managers from the same employees table.

Practical Exercises

Exercise 1: Inner Join

Task: Retrieve the names of employees and their corresponding department names.

Solution:

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

Exercise 2: Left Join

Task: Retrieve all employees and their department names, including employees who do not belong to any department.

Solution:

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

Exercise 3: Full Join

Task: Retrieve all employees and departments, including employees without departments and departments without employees.

Solution:

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

Exercise 4: Self Join

Task: Retrieve the names of employees and their managers.

Solution:

SELECT a.name AS Employee, b.name AS Manager
FROM employees a, employees b
WHERE a.manager_id = b.id;

Common Mistakes and Tips

  • Common Mistake: Forgetting to specify the join condition. Tip: Always ensure you have an ON clause for inner, left, right, and full joins.

  • Common Mistake: Misunderstanding the type of join needed. Tip: Understand the data and the relationships between tables to choose the appropriate join type.

  • Common Mistake: Using cross join unintentionally. Tip: Be cautious with cross joins as they can produce a large number of rows.

Conclusion

Joins are a powerful feature in SQL that allow you to combine data from multiple tables. Understanding the different types of joins and their use cases is crucial for effective database querying. Practice using joins with various datasets to become proficient in writing complex SQL queries.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved