SET OPERATORS
Set operators are used to combine output of two queries. Both the queries must return same number of output and corresponding value must of same data types. Following four types of set operators are provided by SQL:
1) Union:
Set operators are used to combine output of two queries. Both the queries must return same number of output and corresponding value must of same data types. Following four types of set operators are provided by SQL:
1) Union:
Returns unique values available in any of the sets (queries).
2) Intersect:
Returns unique values common to both the sets (queries).
3) Minus:
Returns unique values available in first set but not in second set.
4) Union All:
Returns values from both the sets without suppressing duplicity.
JOINS
A join query is the most important method of solving problems involving two or more tables or single table based on Self Join. Join query overcomes the problem of sub-query of not able to display values from more than one table. Join query based solutions relate tables in Parent-Child hierarchy.
SQL provides four types of Join Query:
1) Equi Join:
When two tables are related to each other based on common column(s).
2) Non-equi Join:
When two tables are related to each other based on values instead of common columns.
3)Self Join:
When single table keeps data related to two entities and two records of same table are related to each other as Parent-Child.
4) Outer Join:
When a join query has to fetch matching records along with non-matching records from tables.
The syntax of the outer join is a bit different from that of the inner join, because it includes a special operator called the outer join operator. The outer join operator is a plus sign enclosed in parentheses, i.e., (+). This operator is used in the join condition in the WHERE clause following a field name from the table that you wish to be considered the optional table. In our suppliers and parts example, the PART table doesn't have information for one supplier. Therefore, we will simply add a (+) operator to the join condition on the side of the PART table. The query and the result set look as follows:
Select s.supplier_id, s.name supplier_name, p.part_nbr, p.name part_namefrom supplier s, part p where s.supplier_id = p.supplier_id (+);
The outer join operator (+) can appear on either the left or the right side of the join condition. However, make sure you apply this operator to the appropriate table in the context of your query.
Join Condition:
A join query must have a join condition that relates one table to another table. Join condition are given with the query, the programmer based on the tables required for the query decides them. Generally number of join conditions are equal to number of tables less one:
Number of Join Conditions = Number of Tables – 1
A join query without join conditions returns number of records equal to product of all records from all tables.
JOINS
A join query is the most important method of solving problems involving two or more tables or single table based on Self Join. Join query overcomes the problem of sub-query of not able to display values from more than one table. Join query based solutions relate tables in Parent-Child hierarchy.
SQL provides four types of Join Query:
1) Equi Join:
When two tables are related to each other based on common column(s).
2) Non-equi Join:
When two tables are related to each other based on values instead of common columns.
3)Self Join:
When single table keeps data related to two entities and two records of same table are related to each other as Parent-Child.
4) Outer Join:
When a join query has to fetch matching records along with non-matching records from tables.
The syntax of the outer join is a bit different from that of the inner join, because it includes a special operator called the outer join operator. The outer join operator is a plus sign enclosed in parentheses, i.e., (+). This operator is used in the join condition in the WHERE clause following a field name from the table that you wish to be considered the optional table. In our suppliers and parts example, the PART table doesn't have information for one supplier. Therefore, we will simply add a (+) operator to the join condition on the side of the PART table. The query and the result set look as follows:
Select s.supplier_id, s.name supplier_name, p.part_nbr, p.name part_namefrom supplier s, part p where s.supplier_id = p.supplier_id (+);
The outer join operator (+) can appear on either the left or the right side of the join condition. However, make sure you apply this operator to the appropriate table in the context of your query.
Join Condition:
A join query must have a join condition that relates one table to another table. Join condition are given with the query, the programmer based on the tables required for the query decides them. Generally number of join conditions are equal to number of tables less one:
Number of Join Conditions = Number of Tables – 1
A join query without join conditions returns number of records equal to product of all records from all tables.
Cartesian Product
If you don't specify the join condition while joining two tables, Oracle combines each row from the first table with each row of the second table. This type of result set is called as a Cartesian product. The number of rows in a Cartesian product is the product of the number of rows in each table.
Here's an example of a Cartesian product:
Select E.Lname, D.Name From Employee E, Department D;
