MySQL for Windows – Join Queries (Inner Join & Outer Join) | FYBSc IT Practical
Aim of the Practical
To perform Join Queries in MySQL for Windows including INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and simulate FULL OUTER JOIN using UNION.
What are Joins in MySQL?
In relational databases, JOIN is used to combine rows from two or more tables based on a related column between them. In MySQL for Windows, JOIN queries are an important part of DBMS practicals for FYBSc IT students.
Sample Tables
Consider the following two tables for demonstration:
Students Table
+----+-------+-----------+
| id | name | course_id |
+----+-------+-----------+
| 1 | Amit | 101 |
| 2 | Sara | 102 |
| 3 | John | 101 |
| 4 | Priya | NULL |
+----+-------+-----------+
Courses Table
+-----------+-------------------+
| course_id | course_name |
+-----------+-------------------+
| 101 | Computer Science |
| 102 | Information Tech |
| 103 | Data Science |
+-----------+-------------------+
1. INNER JOIN
Definition: INNER JOIN returns only the matching records from both tables. If there is no match, that row is not shown.
SELECT students.name, courses.course_name
FROM students
INNER JOIN courses
ON students.course_id = courses.course_id;
Output:
name course_name
Amit Computer Science
Sara Information Tech
John Computer Science
2. LEFT OUTER JOIN
LEFT JOIN returns all records from the left table (students), and the matched records from the right table (courses). If no match is found, NULL is shown.
SELECT students.name, courses.course_name
FROM students
LEFT JOIN courses
ON students.course_id = courses.course_id;
Output:
name course_name
Amit Computer Science
Sara Information Tech
John Computer Science
Priya NULL
Priya has no matching course, so NULL is displayed.
3. RIGHT OUTER JOIN
RIGHT JOIN returns all records from the right table (courses), and the matched records from the left table (students). If no match is found, NULL is shown.
SELECT students.name, courses.course_name
FROM students
RIGHT JOIN courses
ON students.course_id = courses.course_id;
Output:
name course_name
Amit Computer Science
Sara Information Tech
John Computer Science
NULL Data Science
"Data Science" has no matching student, so NULL is displayed.
4. FULL OUTER JOIN (Simulation in MySQL)
MySQL does not directly support FULL OUTER JOIN.
However, we can simulate it using UNION of LEFT JOIN and RIGHT JOIN.
SELECT students.name, courses.course_name
FROM students
LEFT JOIN courses
ON students.course_id = courses.course_id
UNION
SELECT students.name, courses.course_name
FROM students
RIGHT JOIN courses
ON students.course_id = courses.course_id;
Output:
name course_name
Amit Computer Science
Sara Information Tech
John Computer Science
Priya NULL
NULL Data Science
This result shows all students and all courses. If no match is found, NULL is displayed.
Conclusion
In this MySQL for Windows practical, you learned how to perform INNER JOIN, LEFT JOIN, RIGHT JOIN, and simulate FULL OUTER JOIN using UNION. These queries are extremely important for working with multiple related tables in your FYBSc IT DBMS practicals.
👉 Practice these join queries regularly to gain confidence in handling relational databases.
🔗
