Working with multiple tables joins

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 joins

Cross 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 joins

Inner 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 joins

Outer 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-joins

Equi 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 joins

Self 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.
Working with multiple tables
Working with multiple tables - Define subquery. Explain with an example, What is correlated query? Explain its uses, What are Set Operators? Explain union and union all with an example for each...
Post your comment

You may like