You are here
Home > Sql Server > SQL: Set Operators

SQL: Set Operators

SQL: Set Operators

  • Set operators are used to combine same type of data from two or more tables.
  • SQL supports following operators:
  • a) Union

    b) Union all

    c) Intersect

    d) Except

Rules on Set Operators:

  • Column alias must be expressed in the first select statement.
  • Each select statement must have the same number of columns and columns must have same data type.
  • In each select statement columns must be in the same order.

Union:

  • Union combines two or more result sets in to a single set. It removes duplicate rows.
  • Syntax:
  • select column_name(s) from table1

    union

    select column_name(s) from table2

 

  • Database table: Customers
Customer_id Customer_name City
1 Abhi Surat
2 Prince Pune
3 Juhi Ahmedabad
4 Maya Surat

 

  • Database table: Employees
Id Employee_name City
1 Anil Mumbai
2 Prince Pune
3 Juhi Anand
4 Tina Pune

 

  • Example:
  • select Customer_name from Customers

    union

    select Employee_name from Employees;

     

  • Output:
Customer_name
Abhi
Anil
Juhi
Maya
Prince
Tina

 

Union all:

  • Union all combines two or more result sets in to a single set. It does not remove duplicate rows.
  • Syntax:
  • select column_name(s) from table1

    union all

    select column_name(s) from table2

  • Example: Use above database tables Customers and Employees.
  • select Customer_name from Customers

    union all

    select Employee_name from Employees;

     

  • Output:
Customer_name
Abhi
Prince
Juhi
Maya
Anil
Prince
Juhi
Tina

 

Intersect:

  • It only returns common data from both result sets.
  • Syntax:
  • select column_name(s) from table1

    intersect

    select column_name(s) from table2

  • Example: Use above database tables Customers and Employees.
  • select Customer_name, City from Customers

    intersect

    select Employee_name, City from Employees;

  • Output:
Customer_name City
Prince Pune

 

Except:

  • It returns distinct data that are selected by the first result set, but not selected in the second result set.
  • Syntax:
  • select column_name(s) from table1

    except

    select column_name(s) from table2

  • Example: Use above database tables Customers and Employees.
  • select Customer_name, City from Customers

    except

    select Employee_name, City from Employees;

  • Output:
Customer_name City
Abhi Surat
Juhi Ahmedabad
Maya Surat

 

 

Keep visiting my site ask2tech.com for upcoming top technical article

Learn more about DBA at TechNet Consultancy

 

Leave a Reply

Top