Set operation with solutions ( Union, union all, intersect, intersect all, except, except all )

              Set Operation With Solutions   




Union/union all:




* The SQL UNION clause/operator is used to combine the results of two or 

   more  SELECT statements without returning any duplicate rows.
   To use this UNION clause, each SELECT statement must have
  • The same number of columns selected
  • The same number of column expressions
  • The same data type and
  • Have them in the same order


* The UNION ALL operator is used to combine the results of two SELECT 
   statements including duplicate rows.
  The same rules that apply to the UNION clause will apply to the 
  UNION ALL operator.

OUR Tables




1) Find all Customer Name in depositor & borrower table without the duplicate name?
      
    SQL Query: select customer_name from depositor
                         union
                         select customer_name from borrower


   Discription: 
                                                          Depositor & Borrower Table


Our Output in this sql Query





2) Find all Customer Name in depositor & borrower table within the duplicate name?

      
    SQL Query: select customer_name from depositor
                         union all
                         select customer_name from borrower





3) Find all Customer Account Number in depositor & account table without the duplicate Account Number?

      
    SQL Query: select account_number from depositor
                         union 
                         select account_number from account






4) Find all Customer Account Number in depositor & account table within the duplicate Account Number?

      
    SQL Query: select account_number from depositor
                         union all
                         select account_number from account







5) Find all Branch Name in Branch & Account table without the duplicate Branch Name?

      
    SQL Query: select branch_name from branch
                         union 
                         select branch_namer from account




6) Find all Branch Name in Branch & Account table within the duplicate Branch Name?
      
    SQL Query: select branch_name from branch
                         union all
                         select branch_namer from account








Intersect/Intersect all:



We know if 2 side table information is same then matching information show in out output (Using Intersection )


7) Find all Customer Name in depositor & borrower table without the duplicate name?
      
    SQL Query: select customer_name from depositor
                         intersection
                         select customer_name from borrower





Show just the same information in 2 tables. We see the Depositor Table have Smith and Borrower Table have Smith so if you use intersection then our expected output is 2 table matching information (Example: Smith)






We see our 2 table have Hayes, Johnson, Jones & Smith .....







8) Find all Customer Name in depositor & borrower table within the duplicate name?

      
    SQL Query: select customer_name from depositor
                         intersection all
                         select customer_name from borrower








Except/Except all :



The SQL EXCEPT clause/operator is used to combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. This means EXCEPT returns only rows, which are not available in the second SELECT statement.
Just as with the UNION operator, the same rules apply when using the EXCEPT operator. MySQL does not support the EXCEPT operator.

9)


Table 1 − CUSTOMERS Table is as follows.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+
Table 2 − ORDERS table is as follows.
+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+
Now, let us join these two tables in our SELECT statement as shown below.
SQL> SELECT  ID, NAME, AMOUNT, DATE
   FROM CUSTOMERS
   LEFT JOIN ORDERS
   ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
EXCEPT
   SELECT  ID, NAME, AMOUNT, DATE
   FROM CUSTOMERS
   RIGHT JOIN ORDERS
   ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
This would produce the following result.
+----+---------+--------+---------------------+
| ID | NAME    | AMOUNT | DATE                |
+----+---------+--------+---------------------+
|  1 | Ramesh  |   NULL | NULL                |
|  5 | Hardik  |   NULL | NULL                |
|  6 | Komal   |   NULL | NULL                |
|  7 | Muffy   |   NULL | NULL                |
+----+---------+--------+---------------------+



10)



Now, let us join these two tables in our SELECT statement as shown below.
SQL> SELECT  ID, NAME, AMOUNT, DATE
   FROM CUSTOMERS
   LEFT JOIN ORDERS
   ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
EXCEPT ALL
   SELECT  ID, NAME, AMOUNT, DATE
   FROM CUSTOMERS
   RIGHT JOIN ORDERS
   ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Post a Comment

0 Comments