Learn JOIN operators in RDBMS with practical SQL examples. Understand INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, SELF JOIN, UNION, INTERSECT, and MINUS with student table examples.
JOIN Operators in RDBMS
Introduction
JOIN operators in RDBMS are used to combine data from two or more tables based on a related column between them. They help retrieve meaningful information by establishing relationships among tables.
Need for JOIN Operators
- To fetch data from multiple tables
- To avoid data redundancy
- To maintain data consistency
- To support relational database design
Types of JOIN Operators
-
INNER JOIN
Returns only the records that have matching values in both tables.
Syntax
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
Example
SELECT emp.emp_id, emp.name, dept.dept_name
FROM employee emp
INNER JOIN department dept
ON emp.dept_id = dept.dept_id;
-
LEFT OUTER JOIN
Returns all records from the left table and the matching records from the right table. If there is no match, NULL values are returned for the right table.
Syntax
SELECT columns
FROM table1
LEFT JOIN table2
ON condition;
-
RIGHT OUTER JOIN
Returns all records from the right table and the matching records from the left table.
Syntax
SELECT columns
FROM table1
RIGHT JOIN table2
ON condition;
-
FULL OUTER JOIN
Returns all records when there is a match in either the left or right table. Unmatched rows contain NULL values.
Syntax
SELECT columns
FROM table1
FULL OUTER JOIN table2
ON condition;
Note: FULL OUTER JOIN is not directly supported in MySQL and is implemented using UNION.
-
CROSS JOIN
Returns the Cartesian product of both tables.
Syntax
SELECT columns
FROM table1
CROSS JOIN table2;
-
SELF JOIN
A table is joined with itself to compare rows within the same table.
Difference Between JOIN Types
| JOIN Type | Description |
| INNER JOIN | Matching records only |
| LEFT JOIN | All left table records + matching right table records |
| RIGHT JOIN | All right table records + matching left table records |
| FULL JOIN | All records from both tables |
| CROSS JOIN | Cartesian product |
| SELF JOIN | Same table joined with itself |
Advantages of JOIN Operators
- Retrieves related data efficiently
- Reduces data duplication
- Supports database normalization
- Essential for complex SQL queries
Conclusion
JOIN operators are fundamental in RDBMS for retrieving data from multiple related tables. Understanding different types of JOINs helps in writing efficient and accurate SQL queries.
Practical Implementation Using Student Tables
Table: stud_info1
| sid | sname | address | qualification |
| 1 | Ram | Kundal | BA |
| 2 | Sham | Takari | BCA |
| 3 | Pooja | Pune | BBA |
| 4 | Rahul | Pune | BSc |
Table: stud_info2
| sid | sname | address | qualification | age |
| 5 | Pranav | Kundal | BCA | 20 |
| 6 | Sanika | Balawadi | BCA | 19 |
| 1 | Ram | Kundal | BA | 21 |
| 2 | Sham | Takari | BCA | 19 |
Q1. INNER JOIN
SELECT s1.sid, s1.sname, s1.address, s1.qualification, s2.age
FROM stud_info1 s1
INNER JOIN stud_info2 s2
ON s1.sid = s2.sid;
Q2. NATURAL JOIN
SELECT *
FROM stud_info1
NATURAL JOIN stud_info2;
Q3. LEFT OUTER JOIN
SELECT s1.sid, s1.sname, s1.address, s1.qualification, s2.age
FROM stud_info1 s1
LEFT OUTER JOIN stud_info2 s2
ON s1.sid = s2.sid;
Q4. RIGHT OUTER JOIN
SELECT s2.sid, s2.sname, s2.address, s2.qualification, s2.age
FROM stud_info1 s1
RIGHT OUTER JOIN stud_info2 s2
ON s1.sid = s2.sid;
Q5. FULL OUTER JOIN (Using UNION)
SELECT s1.sid, s1.sname, s1.address, s1.qualification, s2.age
FROM stud_info1 s1
LEFT JOIN stud_info2 s2
ON s1.sid = s2.sid
UNION
SELECT s2.sid, s2.sname, s2.address, s2.qualification, s2.age
FROM stud_info1 s1
RIGHT JOIN stud_info2 s2
ON s1.sid = s2.sid;
Q6. UNION Operator
SELECT sid, sname, address, qualification
FROM stud_info1
UNION
SELECT sid, sname, address, qualification
FROM stud_info2;
Q7. INTERSECT Operation (Using INNER JOIN)
SELECT s1.sid, s1.sname, s1.address, s1.qualification
FROM stud_info1 s1
INNER JOIN stud_info2 s2
ON s1.sid = s2.sid
AND s1.sname = s2.sname
AND s1.address = s2.address
AND s1.qualification = s2.qualification;
Q8. MINUS Operation (Using LEFT JOIN)
SELECT s2.sid, s2.sname, s2.address, s2.qualification
FROM stud_info2 s2
LEFT JOIN stud_info1 s1
ON s2.sid = s1.sid
AND s2.sname = s1.sname
AND s2.address = s1.address
AND s2.qualification = s1.qualification
WHERE s1.sid IS NULL;
Q9. CARTESIAN PRODUCT (CROSS JOIN)
SELECT s1.sid AS sid1, s1.sname AS sname1,
s2.sid AS sid2, s2.sname AS sname2
FROM stud_info1 s1
CROSS JOIN stud_info2 s2;
Some More:Â
- POP- Introduction to Programming Using ‘C’
- DS – Data structure Using C
- OOP – Object Oriented ProgrammingÂ
- Java Programming
- DBMS – Database Management System
- RDBMS – Relational Database Management System
- https://defineinfoloop.blogspot.com/?m=1