Monday, November 19

SQL- Union, Intersect, Except



UNION

The UNION operator is used to combine the result-set of two or more SELECT statements.
The SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
(i.e column name,its order and datatype should be similar otherwise it will result in error).

** The UNION operator selects only distinct values by default.

Syntax:

  •  "   SELECT column_name(s) FROM table_name1              // 1st select statement

                UNION 

                SELECT column_name(s) FROM table_name2 "           // 2nd select statement

Or
  •  "   SELECT (*) FROM table_name1                     // 1st select statement

                UNION 

                SELECT (*) FROM table_name2 "                  // 2nd select statement

Example: Consider two tables Store1 and Store2 as shown below


Now by using the below query
  •      SELECT  (*) FROM Store1                    // 1st select statement

               UNION 

               SELECT (*) FROM Store2                    // 2nd select statement

Result:

Use could see that only distinct output is displayed, Toy2 is present in both Tables but in the output of the query duplicate is not produced.

Same query can be written like this also:
  •     SELECT ITEM FROM Store1              // 1st select statement

              UNION 

              SELECT ITEM FROM Store2             // 2nd select statement

Result will be the same, Without column " Qty "


Now try these queries.
  •     SELECT ITEM FROM  Store1            // 1st select statement

              UNION 

              SELECT (*) FROM Store2                // 2nd select statement

Result: ERROR


  •   SELECT ITEM FROM Store1           // 1st select statement

            UNION 

            SELECT Qty FROM Store2              // 2nd select statement

Result: ERROR
** The error happens because of different no. of columns and when no. of columns are same, the datatypes are different.


UNION ALL

Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

Syntax:

  •  "   SELECT column_name(s) FROM table_name1         // 1st select statement


                UNION ALL

                SELECT column_name(s) FROM table_name2 "      // 2nd select statement

Or
  •  "   SELECT (*) FROM table_name1            // 1st select statement

                UNION ALL

                SELECT (*) FROM table_name2 "         // 2nd select statement

Consider same example Tables, Result Table will contain all records including duplicates as shown below

Note: The Union operator suppresses duplicates, while Union All does not.


INTERSECT

Intersect returns the common record Toy2 from the two tables. It shows just one copy of the record, not both of them. This operation can be used see if you have the same data in more than one table.

Syntax:

  •    SELECT *  FROM Store1
                Intersect

                SELECT *  FROM Store2  "

Result:


EXCEPT

Except shows all of the records from Table1(Store1) that do not appear in Table2(Store2). Toy2 is therefore eliminated. Toy7, Toy8, Toy9 from Table2(Store2) does not qualify as it’s not in Table1(Store1). 

Syntax:

  •    " SELECT *  FROM Store1
                Except
                    SELECT *  FROM Store2  "

    Result:


    Now see this:

    Syntax:

    •    " SELECT *  FROM Store2 
                    Except
                      SELECT *  FROM Store1  "



      This shows all of the records from Table2(Store2) that do not appear in Table1(Store1). Toy2 is therefore eliminatedToy1, Toy3, Toy4, Toy5, Toy6, from Table1(Store1) does not qualify as it’s not in Table2(Store2). Therefore result will be:


      **Hope this will help...