Joins

Union :Works on identical tables only 1.Select * from a union select * from b: Returns non duplicate values 2.select * from a union all select * from b: Returns duplicates as well. 3.select * from A intersect corresponding by (col1)select * from BAbove detects rows in table A that are duplicated in table B. 4.select * from Aexcept Corresponding by(col1)select * from BThis once will return rows from A whose corresponding calues are not present in B. JOINS : Works when the tables are not union compatible or union compatible. 1. select * from A, BJoins every row of table A with every row of table B. 2. select * from A a, B b where a.col1=b.col2Equijoin, only rows which match the condition are returned. 3. select * from A a, B b where a.col1=b.col1 Or select a.*, b.col1 from A a, Natural Join B bNatural Join :In this all columns that have the same name, type and length are compared with the other table. 4. Select * from A a, B b where a.col1 > b.col2Condition Join:Same as equijoin except the condition is something other then equality. 5. Select * from A a Join B b using( col1, col2)Column Name Join :Just like a natural join however we can specify the same-name to be used. 6. Inner Join select * from A inner join B using (col1, col2)Inner join is a join in which the rows in A and B which do not match the condition do not end up in the result. 7. Outer Join : In this the join does not remove the records in table A and table B that do not match the condition. 7a. Left Outer Join: The left outer join presearves unmatched rows from the left table but discards from the right table. Select * from A a, left outer join B b on a.col1=b.col2//There is no left inner join, 7b. Right Outer Join: It preserves unmatched rows from the right table but discards from the left table. select Select * from A a, right join B b on a.col1=b.col2 7c. Full outer Join: Preserves unmatched rows from left and unmatched rows from right. 8. On Clause vs the where clause: Union:Does not have on clause since it does not filter data. Inner Join: The on and where clauses are logically equivalant here. Outer Joins: The where clause only filters the records. The on clause first filter the records and then includes the rejected records, extended with nulls. 9. Clusturing Index: A table can only have one clustering index. The records in the clustering index are situated next to each other on the disk. So pulling the records are much faster. If the on conditions use the same index, then the records will do a merge join which reads both the tables in order.However one disadvantage is that indexes need to be rebuilt after a lot of updates have been performed.