Set Operations

Set operations : Combination of two or more select queries

Rules for set operations :

- Both select statements should have equal no of columns in count.
- Column order should be same.

SQL> select * from s1;

NAME                    NO ADDRESS                                            
--------------- ---------- -----------------                                  
kamlesh                  1       bangalore                                          
mohan                    2       patna                                              
ravi                        3       delhi                                              

SQL> select * from s2;

NAME                    NO ADDRESS                                            
--------------- ---------- -----------------                                  
kamlesh                  1 bangalore                                          
mohan                    2 patna                                              

Union all : To select all data from all tables including duplicates.

SQL> select name,no,address from s1
  2  union all
  3  select name,no,address from s2;

NAME                    NO ADDRESS                                            
--------------- ---------- -----------------                                  
kamlesh                  1 bangalore                                          
mohan                    2 patna                                              
ravi                         3 delhi                                              
kamlesh                  1 bangalore                                          
mohan                    2 patna                                              

Union : Selects all data from tables excluding duplicates.

SQL> select name,no,address from s1
  2  union
  3  select name,no,address from s2;

NAME                    NO ADDRESS                                            
--------------- ---------- -----------------                                  
kamlesh                  1 bangalore                                          
mohan                    2 patna                                              
ravi                         3 delhi                                              

Minus: Selects records which is not present in second table.

SQL> select name,no,address from s1
  2  minus
  3  select name,no,address from s2;

NAME                    NO ADDRESS                                            
--------------- ---------- -----------------                                  
ravi                     3 delhi                                              

Intersect : Selects only common records from the tables.

SQL> select name,no,address from s1
  2  intersect
  3  select name,no,address from s2;

NAME                    NO ADDRESS                                            
--------------- ---------- -----------------                                  
kamlesh                  1 bangalore                                          
mohan                    2 patna