SQL MINUS

SQL Using MINUS

MINUS - returns results from sets of queries. It shows the rows from one query or another, without the values that appear in the other query.

Pictorial representation :

A skeleton version of MINUS syntax is this:
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
MINUS
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
[ORDER BY column1, column1, ...] 
Example of using MINUS:
SQL server, PostgreSQL and MySQL does not support MINUS.
Oracle:
select CONCAT(Last_name,CONCAT(' ',First_name)) as Name,
   Telephone_type "Phone type",
   Telephone_number as "Phone number",
   us_state_nm as "USA State"
 FROM customers  c
 JOIN telephones t ON (c.Customer_Id=t.Customer_Id)
 JOIN Addresses a ON (c.Customer_Id=a.Customer_Id)
 JOIN US_states u ON (a.US_State_cd=u.US_State_cd)
 WHERE Address_type='Shipping'
MINUS
 select CONCAT(Last_name,CONCAT(' ',First_name)) as Name,
   Telephone_type "Phone type",
   Telephone_number as "Phone number",
   us_state_nm as "USA State"
 FROM customers  c
 JOIN telephones t ON (c.Customer_Id=t.Customer_Id)
 JOIN Addresses a ON (c.Customer_Id=a.Customer_Id)
 JOIN US_states u ON (a.US_State_cd=u.US_State_cd)
 WHERE Address_type='Home'
ORDER BY Name;
The result should be:
name Phone type Phone number USA State
Bowman Judith Cell (371) 153-54 New Jersey
Bowman Judith Work (371) 143-54 New York
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.