Day 58: Joins in SQL – Inner, Outer, and Cross Joins with Examples | Class 11, Chapter 6 - BSEB Class 11-12 Computer Science: 100-Day Bilingual Guide with MCQs, Solutions and Questions

Day 58 – Joins in SQL: Inner, Outer, and Cross Joins

Class 11 Computer Science – Chapter 6

What are Joins in SQL? (SQL में Joins क्या हैं?)

Joins in SQL allow us to combine rows from two or more tables based on a related column. They help in retrieving meaningful data by integrating datasets from normalized tables in relational databases.
SQL में Joins का उपयोग दो या अधिक टेबल्स की पंक्तियों को संबंधित कॉलम के आधार पर जोड़ने के लिए किया जाता है। यह सामान्यीकृत टेबल्स से डेटा को जोड़कर सार्थक जानकारी पुनः प्राप्त करने में मदद करता है।

Why are Joins Important? (Joins क्यों महत्वपूर्ण हैं?)

• To establish relationships between tables in a database.
  डेटाबेस में टेबल्स के बीच संबंध स्थापित करने के लिए।
• To reduce data redundancy by normalizing tables.
  टेबल्स को सामान्यीकृत करके डेटा पुनरावृत्ति को कम करने के लिए।
• To fetch combined data from multiple tables for complex queries.
  जटिल क्वेरीज़ के लिए कई टेबल्स से सम्मिलित डेटा पुनः प्राप्त करने के लिए।

Types of Joins (Joins के प्रकार)

• Inner Join: Returns rows with matching values in both tables.
  Inner Join: दोनों टेबल्स में मेल खाने वाले मानों वाली पंक्तियों को वापस करता है।
• Outer Join: Includes unmatched rows from one or both tables in addition to matched rows.
  Outer Join: मेल खाने वाले मानों के अलावा एक या दोनों टेबल्स से असंगत पंक्तियों को शामिल करता है।
  • Left Outer Join
  • Right Outer Join
  • Full Outer Join
• Cross Join: Returns the Cartesian product of two tables.
  Cross Join: दो टेबल्स का कार्टेशियन उत्पाद देता है।

Examples of Joins in SQL (SQL में Joins के उदाहरण)

Example Tables:

Table 1: Employees

| EmployeeID | Name     | DepartmentID |
|------------|----------|--------------|
| 1          | Alice    | 101          |
| 2          | Bob      | 102          |
| 3          | Charlie  | NULL         |

Table 2: Departments

| DepartmentID | DepartmentName |
|--------------|----------------|
| 101          | HR             |
| 102          | IT             |
| 103          | Finance        |

1. Inner Join

Query:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Output:

| Name   | DepartmentName |
|--------|----------------|
| Alice  | HR             |
| Bob    | IT             |

2. Left Outer Join

Query:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Output:

| Name     | DepartmentName |
|----------|----------------|
| Alice    | HR             |
| Bob      | IT             |
| Charlie  | NULL           |

3. Right Outer Join

Query:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Output:

| Name     | DepartmentName |
|----------|----------------|
| Alice    | HR             |
| Bob      | IT             |
| NULL     | Finance        |

4. Cross Join

Query:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
CROSS JOIN Departments;

Output:

| Name     | DepartmentName |
|----------|----------------|
| Alice    | HR             |
| Alice    | IT             |
| Alice    | Finance        |
| Bob      | HR             |
| Bob      | IT             |
| Bob      | Finance        |
| Charlie  | HR             |
| Charlie  | IT             |
| Charlie  | Finance        |

Practice Questions

Multiple Choice Questions (MCQs)

1. Which join returns only matching rows from both tables?
   (a) Inner Join | (b) Left Join | (c) Right Join | (d) Full Join
2. What does a cross join return?
   (a) Matching rows | (b) Cartesian product | (c) Unmatched rows | (d) None
3. Which join includes unmatched rows from the left table?
   (a) Inner Join | (b) Left Join | (c) Right Join | (d) Full Join
4. What is the output of a full outer join when no match is found?
   (a) Matching rows only | (b) NULL for unmatched rows | (c) Unmatched rows only | (d) None
5. What happens if there is no matching row in a right join?
   (a) NULL is displayed for unmatched rows | (b) Only matched rows are displayed | (c) Both rows are displayed | (d) None
6. Which join retrieves all rows from the left table and only matching rows from the right?
   (a) Left Outer Join | (b) Inner Join | (c) Full Outer Join | (d) None
7. What is the purpose of the ON clause in joins?
   (a) To filter rows | (b) To define the join condition | (c) To specify output columns | (d) None
8. What happens if a join condition is not specified in a cross join?
   (a) It produces an error | (b) It generates a Cartesian product | (c) It returns NULL | (d) None
9. Which join is suitable to find unmatched rows in both tables?
   (a) Cross Join | (b) Full Outer Join | (c) Inner Join | (d) None
10. How can you retrieve only unmatched rows in a left join?
    (a) Use WHERE column IS NULL | (b) Use ON column=NULL | (c) Use SELECT DISTINCT | (d) None

Answers to MCQs:

1: (a), 2: (b), 3: (b), 4: (b), 5: (a), 6: (a), 7: (b), 8: (b), 9: (b), 10: (a)

Short Answer Questions

1. What is an inner join? Provide an example query.
   Answer: Inner Join returns rows with matching values in both tables. Example query:

   SELECT Employees.Name, Departments.DepartmentName
   FROM Employees
   INNER JOIN Departments
   ON Employees.DepartmentID = Departments.DepartmentID;
           

2. Differentiate between Left Join and Right Join.
   Answer: Left Join includes all rows from the left table and matching rows from the right. Right Join includes all rows from the right table and matching rows from the left.
3. Write the syntax of a Full Outer Join.
   Answer:

   SELECT column1, column2
   FROM table1
   FULL OUTER JOIN table2
   ON table1.common_column = table2.common_column;
           

4. What is the purpose of a Cross Join?
   Answer: A Cross Join returns the Cartesian product of two tables.
5. Why are joins important in SQL?
   Answer: Joins combine data from multiple tables, reduce redundancy, and help retrieve meaningful information efficiently.

Long Answer Questions

1. Explain the types of SQL joins with real-world examples.
2. Discuss the benefits of joins in relational databases with use cases.
3. Write SQL queries to demonstrate inner, outer, and cross joins using sample tables.