www.bullraider.com

Set Operators

AddThis Social Bookmark Button
  • Set operators basically combine the result of two queries into one
  • These queries are known as compound queries.

These are the set operater.

UNION: Returns all rows from either queries; no duplicate rows.
UNION ALL: Returns all rows from either query, including duplicates.
INTERSETCT: Returns distinct rows that are returned by both queries.
MINUS: Returns distinct rows that are returned by the first query but not returned by the second.

Example

Query1 Select ename  from emp where dept_id=40;

This query returns result

 Alex

Peter

John

Richa

Query2 Select ename  from emp where sal>=4000;

This query returns result

 Aled

Peter

John

Chrish

Union: If we'll write Query1 Union Query2 Like

(Select ename  from emp where dept_id=40) Union ( Select ename  from emp where sal>=4000)

This will return result

 Alex

Peter

John

Richa

Aled

Chrish

UNION ALL: If we'll write Query1 Union  All Query2 Like

(Select ename  from emp where dept_id=40) Union All ( Select ename  from emp where sal>=4000)

This will return

 Alex

Peter

John

Richa

Aled

Peter

John

Chrish

INTERSETCT: If we'll write Query1 INTERSETCT  Query2 Like

(Select ename  from emp where dept_id=40) INTERSETCT ( Select ename  from emp where sal>=4000)

This will return

Peter

John

MINUS:If we'll write Query1 MINUS  All Query2 Like

(Select ename  from emp where dept_id=40)  MINUS  ( Select ename  from emp where sal>=4000)

This will return

 Alex

Richa


Powered by Bullraider.com