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 |
Next in menu
© 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.