Meritshot Tutorials

  1. Home
  2. »
  3. SQL JOINS
SQL

SQL Tutorial

SQL JOINS

In SQL, a JOIN clause allows you to combine rows from two or more tables based on a common column between them. This is useful when data is spread across multiple tables but needs to be analyzed together.

Example Tables:

Consider the following “Orders” table:

OrderID

CustomerID

OrderDate

101

45

2024-09-18

102

66

2024-09-19

103

98

2024-09-20

And a corresponding “Customers” table:

CustomerID

CustomerName

ContactName

Country

1

Gupta Kirana Store

Rajesh Gupta

India

2

Sharma Sweets

Priya Sharma

India

3

Patel Groceries

Amit Patel

India

 

The CustomerID column in the “Orders” table relates to the CustomerID in the “Customers” table, creating a relationship between the two tables.

Types of SQL JOINs

  1. INNER JOIN: Returns only the rows where there is a match in both tables.
  2. 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.
  3. 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.
  4. FULL OUTER JOIN: Returns all rows when there is a match in either left or right table.
  5. SELF JOIN: Joins a table with itself, useful for finding relationships within the same table.

1.SQL INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables.

Example 1:

Query:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders

INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Explanation:

  • Objective: Fetch the Order ID, Customer Name, and Order Date for orders where there is a matching Customer ID in both the “Orders” and “Customers” tables.
  • How it works: This query retrieves only those orders that have a corresponding entry in the “Customers” table. The INNER JOIN ensures that only records with matching CustomerID in both tables are included.
  • Result:

OrderID

CustomerName

OrderDate

10308

Sharma Sweets

1996-09-18

10309

Patel Groceries

1996-09-19

Here, the order with OrderID 10310 is excluded because there is no matching CustomerID in the “Customers” table.

Example 2:

Query:

SELECT Employees.EmpName, Departments.DeptName

FROM Employees

INNER JOIN Departments ON Employees.DeptID = Departments.DeptID;

Explanation:

  • Objective: Retrieve the employee names and their corresponding department names.
  • How it works: This query will return only those employees who are associated with a department (i.e., where there is a matching DeptID in both the “Employees” and “Departments” tables). The INNER JOIN filters out employees who don’t have a corresponding department.

Example 3:

Query:

SELECT Students.StudentName, Courses.CourseName

FROM Students

INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID

INNER JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

Explanation:

  • Objective: Get a list of students along with the courses they are enrolled in.

How it works: This query first joins the “Students” and “Enrollments” tables to find which courses each student is enrolled in. Then, it joins this result with the “Courses” table to fetch the course names. The INNER JOIN ensures that only students who are enrolled in at least one course are included.

2.SQL LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table (first table listed) and the matched rows from the right table. If there is no match, NULL values are returned for columns from the right table.

Example 1:

Query:

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers

LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Explanation:

  • Objective: List all customers and their orders, if any.
  • How it works: This query will return all customers from the “Customers” table, along with the corresponding OrderID from the “Orders” table. If a customer hasn’t placed an order, the OrderID will show as NULL. The LEFT JOIN ensures that all customers are included, even if they haven’t placed any orders.
  • Result:

CustomerName

OrderID

Gupta Kirana Store

NULL

Sharma Sweets

10308

Patel Groceries

10309

Here, “Gupta Kirana Store” appears in the result even though they have not placed any orders.

Example 2:

Query:

SELECT Teachers.TeacherName, Classes.ClassName

FROM Teachers

LEFT JOIN Classes ON Teachers.ClassID = Classes.ClassID;

Explanation:

  • Objective: Display a list of teachers and the classes they are associated with, if any.
  • How it works: This query will include all teachers, even those who are not currently assigned to any class. If a teacher is not assigned to a class, the ClassName will appear as NULL.

Example 3:

Query:

SELECT Authors.AuthorName, Books.BookTitle

FROM Authors

LEFT JOIN Books ON Authors.AuthorID = Books.AuthorID;

Explanation:

  • Objective: Show a list of authors and their corresponding books.
  • How it works: This query ensures all authors are listed, even if they haven’t written any books. If an author has no books, the BookTitle will be NULL.

3.SQL RIGHT JOIN

The RIGHT JOIN keyword returns all rows from the right table (second table listed) and the matched rows from the left table. If there is no match, NULL values are returned for columns from the left table.

Example 1:

Query:

SELECT Orders.OrderID, Employees.EmpName

FROM Orders

RIGHT JOIN Employees ON Orders.EmpID = Employees.EmpID;

Explanation:

  • Objective: List all employees and the orders they have processed, if any.
  • How it works: This query retrieves all employees from the “Employees” table, along with the orders they processed. If an employee hasn’t processed any orders, the OrderID will show as NULL. The RIGHT JOIN ensures that all employees are included, regardless of whether they have processed an order.
  • Result:

OrderID

EmpName

NULL

Manish Sharma

10308

Alok Kumar

NULL

Pooja Singh

Here, “Manish Sharma” and “Pooja Singh” appear in the result even though they haven’t processed any orders.

Example 2:

Query:

SELECT Attendance.AttendanceDate, Students.StudentName

FROM Attendance

RIGHT JOIN Students ON Attendance.StudentID = Students.StudentID;

Explanation:

  • Objective: Show a list of all students and their attendance records, if any.
  • How it works: This query includes all students from the “Students” table, along with their attendance records. If a student hasn’t attended any classes, the AttendanceDate will be NULL.

Example 3:

Query:

SELECT Projects.ProjectName, Employees.EmpName

FROM Projects

RIGHT JOIN Employees ON Projects.EmpID = Employees.EmpID;

Explanation:

  • Objective: Display all employees and the projects they are working on, if any.
  • How it works: This query lists all employees and their corresponding projects. If an employee isn’t assigned to a project, the ProjectName will be NULL.

4.SQL FULL OUTER JOIN

The FULL OUTER JOIN keyword returns all rows when there is a match in either the left or right table. Rows from both tables that do not match will appear with NULL values in the respective columns.

Example 1:

Query:

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers

FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Explanation:

  • Objective: Combine all customers and all orders, showing which customers placed which orders, if any.
  • How it works: This query returns all customers from the “Customers” table and all orders from the “Orders” table. If a customer did not place an order or if an order does not have a matching customer, NULL values are returned for the missing data. The FULL OUTER JOIN ensures that all records from both tables are included.
  • Result:

CustomerName

OrderID

Gupta Kirana Store

NULL

Sharma Sweets

10308

NULL

10309

Here, “Gupta Kirana Store” appears in the result despite having no orders, and an order with no matching customer also appears.

Example 2:

Query:

SELECT Employees.EmpName, Projects.ProjectName

FROM Employees

FULL OUTER JOIN Projects ON Employees.EmpID = Projects.EmpID;

Explanation:

  • Objective: Display all employees and all projects, including those not currently assigned to any employee.
  • How it works: This query lists all employees and all projects. If an employee is not assigned to a project, or if a project has no assigned employees, NULL values will appear for the missing data.

Example 3:

Query:

SELECT Authors.AuthorName, Books.BookTitle

FROM Authors

FULL OUTER JOIN Books ON Authors.AuthorID = Books.AuthorID;

Explanation:

  • Objective: Show all authors and all books, even if some authors haven’t written any books or some books have no listed authors.
  • How it works: This query includes all records from both the “Authors” and “Books” tables. If an author hasn’t written a book, or if a book has no author listed, NULL values will appear in the corresponding columns.

5.SQL SELF JOIN

A SELF JOIN is a regular join but the table is joined with itself. It’s useful when you need to find related data within the same table.

Example 1:

Query:

SELECT A.EmpName AS Employee, B.EmpName AS Manager

FROM Employees A, Employees B

WHERE A.ManagerID = B.EmpID;

Explanation:

  • Objective: Find the manager of each employee.
  • How it works: This query joins the “Employees” table with itself to match each employee with their manager. Here, A and B are aliases for the same “Employees” table. The WHERE clause specifies that an employee’s ManagerID should match another employee’s EmpID.
  • Result:

Employee

Manager

Alok Kumar

Pooja Singh

Pooja Singh

Manish Sharma

Here, each employee is paired with their manager.

Example 2:

Query:

SELECT A.StudentName AS Student, B.StudentName AS Mentor

FROM Students A, Students B

WHERE A.MentorID = B.StudentID;

Explanation:

  • Objective: List students along with their mentors, who are also students.
  • How it works: This query uses a self-join to pair students with their mentors. The A and B aliases represent the same “Students” table, allowing us to find which student is mentoring another.

Example 3:

Query:

SELECT A.ProductName AS Product, B.ProductName AS RelatedProduct

FROM Products A, Products B

WHERE A.CategoryID = B.CategoryID AND A.ProductID <> B.ProductID;

Explanation:

  • Objective: Find related products within the same category.
  • How it works: This query joins the “Products” table with itself to identify products that belong to the same category but are different from each other. The WHERE clause ensures that the products are from the same category but have different ProductID.

6 CROSS JOIN

The CROSS JOIN keyword returns the Cartesian product of two tables, which means it returns all possible combinations of rows from the two tables. Each row in the first table is combined with each row in the second table.

Example Tables:

Consider the following “Products” table:

ProductID

ProductName

1

Laptop

2

Tablet

And a “Colors” table:

ColorID

ColorName

1

Red

2

Blue

SQL CROSS JOIN Examples

Example 1:

Query:

SELECT Products.ProductName, Colors.ColorName

FROM Products

CROSS JOIN Colors;

Explanation:

  • Objective: Find all possible combinations of products and colors.
  • How it works: This query returns every possible pairing of products with colors. Since there are 2 products and 2 colors, the result will include 4 rows, with each product paired with each color.
  • Result:

ProductName

ColorName

Laptop

Red

Laptop

Blue

Tablet

Red

Tablet

Blue

Here, each product is paired with every color, showing all possible combinations.

Example 2:

Query:

SELECT Employees.EmpName, Departments.DeptName

FROM Employees

CROSS JOIN Departments;

Explanation:

  • Objective: Create a list of every possible employee-department pairing.
  • How it works: This query generates all possible combinations of employees and departments. If there are 3 employees and 4 departments, the result will contain 12 rows, with each employee paired with each department.

Example 3:

Query:

SELECT Courses.CourseName, Students.StudentName

FROM Courses

CROSS JOIN Students;

Explanation:

  • Objective: List all possible combinations of courses and students.
  • How it works: This query pairs each course with each student, producing a Cartesian product of courses and students. If there are 5 courses and 10 students, the result will have 50 rows, with every course associated with every student.

Key Points About CROSS JOIN

  1. Cartesian Product: The CROSS JOIN returns a Cartesian product, meaning if Table A has m rows and Table B has n rows, the result will have m * n rows.
  2. Use Cases: It is often used in scenarios where you need to generate combinations of rows from two tables. For example, creating a list of all possible pairs of products and colors for inventory or marketing purposes.
  3. Performance Consideration: Be cautious with CROSS JOIN for large tables as the number of resulting rows can grow exponentially, potentially leading to performance issues.

Conclusion

SQL JOINs are essential tools for combining data from multiple tables, allowing you to retrieve and analyze related information effectively. Each type of JOIN serves a specific purpose and is suitable for different scenarios:

  1. INNER JOIN: Retrieves rows with matching values in both tables, making it ideal for scenarios where you only need to see records that have corresponding entries in both tables.
  2. LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and the matched rows from the right table, including unmatched rows from the left table. This is useful when you need all records from one table regardless of whether they have corresponding entries in another table.
  3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and the matched rows from the left table, including unmatched rows from the right table. It’s helpful when you need to include all records from one table even if there are no matches in the other table.
  4. FULL OUTER JOIN: Combines the results of both LEFT JOIN and RIGHT JOIN, returning all rows when there is a match in either table. This type of join is beneficial when you need to see all records from both tables, including those without matches.
  5. CROSS JOIN: Produces a Cartesian product of the two tables, meaning every row from the first table is paired with every row from the second table. This join is useful for generating combinations of rows, though it can produce a large number of results if the tables are sizable.

Understanding these JOIN types and their applications helps in constructing queries that accurately reflect the relationships between tables and meet the specific needs of your data analysis. By leveraging the appropriate JOIN, you can efficiently combine and analyze data to gain meaningful insights and make informed decisions.

Frequently Asked Questions

❮ Previous
Next ❯
ads website