Practical- 4
4. Use of select query on two relations
In relational databases, a relationship exists between two tables when one of them has a foreign
key that references the primary key of the other table. This single fact allows relational
databases to split and store data in different tables, yet still link the disparate data items
together.
JOIN: A SQL Join statement is used to combine data or rows from two or more tables based on
a common field between them.
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• SELF JOIN
To apply join query on relations, first create two or more tables Create two tables that have
one or more than one common row in them Table
Output
Two tables created student1 or
student2 emp1
[Link] JOIN: The INNER JOIN keyword selects all rows from both the tables as long as
the condition satisfies. This keyword will create the result-set by combining all rows from both
the tables where the condition satisfies i.e value of the common field will be same.
Syntax:
SELECT student1.student_name, student2.student_rollno
FROM student2
INNER JOIN student1
ON student2.student_rollno = student1.student_rollno;
[Link] JOIN: This join returns all the rows of the table on the left side of the join and
matching rows for the table on the right side of join. The rows for which there is no matching
row on right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER
JOIN.
Syntax:
SELECT student2.student_name, student1.student_rollno
FROM student2
LEFT JOIN student1
ON student1.student_name = student2.student_name;
[Link] JOIN: RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the
table on the right side of the join and matching rows for the table on the left side of join. The
rows for which there is no matching row on left side, the result-set will contain null. RIGHT
JOIN is also known as RIGHT OUTER JOIN.
Syntax:
SELECT student2.student_name, student1.student_rollno
FROM student2
RIGHT JOIN student1
ON student1.student_rollno = student2.student_rollno;
[Link] JOIN: FULL JOIN creates the result-set by combining result of both LEFT JOIN
and RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for
which there is no matching, the result-set will contain NULL values.
Syntax:
SELECT student1.student_name, student2.student_name
FROM student1
LEFT JOIN student2 ON student1.student_rollno = student2.student_rollno
UNION
SELECT student1.student_name, student2.student_name
FROM student2
LEFT JOIN student1 ON student1.student_rollno = student2.student_rollno;
[Link] Join:
The SQL SELF JOIN is used to join a table to itself as if the table were two tables; temporarily
renaming at least one table in the SQL statement
Syntax:
SELECT student1.student_name, student1.student_rollno, student2.student_name
FROM student1, student2
WHERE student1.student_rollno = student2.student_rollno;
