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;

<-- Back Page