What are joins? Explain its characteristic features
Joins are used to combine data of one or more tables. Joins should be used when there is abundant data. Joins can be LEFT, RIGHT, OUTER, INNER or even SELF JOIN. The purpose is to bind data from multiple tables without any receptivity.
Types of joins. Define, write syntax and an example for the following types
Cross joinsCross joins return a Cartesian product of sets of rows from joined tables. This means, each row from the first table is combined with each row from the second table.
Syntax:
SELECT * FROM [TABLE 1] CROSS JOIN [TABLE 2]
Example:
Table1 values –
1, John, NewYork
2, David,Raleigh
Table2 values -
1, 1(FK), 20,000(salary), development (branch)
2, 2,25,000, analyst
Result-
1, John, NewYork, 1, 20,000, development
2, David, Raleigh, 1, 2,25,000, analyst
Inner joinsInner joins returns a result set when there is at least one match in both tables.
Syntax:
SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name
Example:
Table1 values –
1, John, NewYork
2, David,Raleigh
Table2 values-
1, 1(FK), 20,000(salary), development (branch)
2, 2, 25,000, analyst
3, 2, 40,000, coder
Select table1.name, table1.city, table2.role From table1 INNER JOIN table2 ON table1.id=table2.id
Result:
1, John, NewYork, 1, 20,000, development
2, David, Raleigh, 1, 2, 25,000, analyst
Outer joinsOuter joins returns a result set of both tables irrespective of a match.
Syntax:
SELECT column_name(s) FROM table_name1 OUTER JOIN table_name2 ON table_name1.column_name=table_name2.column_name
Example:
Table1 values –
1, John, NewYork
2, David,Raleigh
3, Tom, mephis
Table2 values-
1, 1(FK), 20,000(salary), development (branch)
2, 2, 25,000, analyst
Select table1.name, table1.city, table2.role From table1 OUTER JOIN table2 ON table1.id=table2.id
Result:
1, John, NewYork, 1, 20,000, development
2, David, Raleigh, 1, 2,25,000, analyst
3, Tom, mephis, , ,
Equi- and non-equi-joinsEqui joins use equality operators when making a comparison in the join predicate.
Example:
Table1 values –
1, John, NewYork
2, David,Raleigh
3, Tom, mephis
Table2 values-
1, 1(FK), 20,000(salary), development (branch)
2, 2, 25,000, analyst
select * from table1,table2 where table1.id=table2.id;
Non Equi joins are used to return results when an exact join is not possible. This join uses a non equi operator like !=, <>, BETWEEN etc
Example:
Table1 values –
1, John, NewYork
2, David,Raleigh
3, Tom, mephis
Table2 values-
1, 1(FK), 20,000(salary), development (branch)
2, 2, 25,000, analyst
select * from table1,table2 where table1.id != table2.id;
Self joinsSelf join is a join on the same table. They are a simpler replacement to nested queries referencing the same table. Such joins are used when the table references itself.
Example:
Table student has columns student_id, student_name, professor
Values:
1, John,2
2, Peter,3
3, Tim,2
Here, the professor attribute simply references the student ID of another student in the same table.
Partition outer joins Partition outer join is introduced in Oracle 10g used to densify certain dimensions of a table while keeping others sparse. The result is a UNION of the outer joins of each of the partitions in the partitioned result set and the table on the other side of the join .query_partition_clause that can be on either side of the outer join is used to define a portioned outer join.
