Retrieve Data from Multiple Tables using the following join operations • Cartesian Products • Equijoin • Non-equijoin • Outer join • Self join.

 

EXERCISE : 7

AIM : Retrieve Data from Multiple Tables using the following join operations
• Cartesian Products            • Equijoin                      • Non-equijoin
• Outer join                          • Self join.

Description :

Cartesian product :

Join:-A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian product.

If WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.

Syntax:- SELECT * FROM table1 CROSS JOIN table2;

Example:

Select * from student, enroll;

Output:

Equijoin:

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

Syntax:

Example: select * from student INNER JOIN enroll on student.sid=enroll.sid;

Output:

Non-equijoin:

Theta Join allows you to merge two tables based on the condition represented by theta. Theta joins work for all comparison operators.

The general case of JOIN operation is called a Theta join. It is denoted by symbol θ

Syntax: Select column_names from table1 join table2 on table1.col1>table2.col1

Example: select * from student join enroll on student.sid>enroll.sid;

Output:

 

Outer join:

1. When performing an inner join, rows from either table that are unmatched in the other table are not returned.

2. In an outer join, unmatched rows in one or both tables can be returned. There are a few types of outer joins:

ü  LEFT JOIN returns only unmatched rows from the left table.

ü  RIGHT JOIN returns only unmatched rows from the right table.

ü  FULL OUTER JOIN returns unmatched rows from both tables

LEFT JOIN(left outer join):-

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

Syntax:-

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

Examples:

 

RIGHT JOIN(right outer join):-

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

Syntax:-

SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Examples:

select * from student right join enroll on student.sid=enroll.sid;

insert into enroll values (107,'cs107');

 

Full Outer Join:

The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.

Note: FULL OUTER JOIN can potentially return very large result-sets!

Syntax:-

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;

Example: select * from student full join enroll on student.sid=enroll.sid;

Output:

Self join: self JOIN is a regular join, but the table is joined with itself.

Syntax:

SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;

Note:-T1 and T2 are different table aliases for the same table.

Example:

Output:

 

 

 

VIVA QUESTIONS:

1. What is Cartesian Product?

2. Differentiate is Equi join & Non-Equi Join?

3. Describe about Self join