Description
SQL Except will return all the result obtained from first SQL explanation that are not returned by second SELECT articulation. EXCEPT operator will retrieve all records from the first dataset obtained from first select query and then remove from the results all records from the second dataset.
Conceptual
figure
Syntax
The syntax for SQL except clause is as follows
SELECT column1, column2.....columnN FROM <table_name>..... WHERE <search_condition>; EXCEPT SELECT column1, column2.....columnN FROM <table_name>..... WHERE <search_condition>;
Examples
The below example describes the execution of SQL Except clause.
[c]
sql> select * from customers;
+---------+-----------+------+--------+
| cust_id | cust_name | age | salary |
+---------+-----------+------+--------+
| 1 | Jack | 23 | 12000 |
| 2 | James | 24 | 13000 |
| 3 | Mack | 25 | 14000 |
| 4 | Maddie | 26 | 15000 |
+---------+-----------+------+--------+
4 rows in set (0.00 sec)
sql> select * from orders;
+----------+---------+--------+
| order_id | cust_id | amount |
+----------+---------+--------+
| 1 | 2 | 15000 |
| 2 | 4 | 12000 |
| 3 | 7 | 16000 |
+----------+---------+--------+
3 rows in set (0.00 sec)
sql>select cust_id,cust_name,amount from customers left join orders on customers.cust_id =orders.cust_id except select cust_id,cust_name,amount from customers right join orders on customers.cust_id =orders.cust_id;
+----------+----------+--------+
| cust_id | cust_name | amount |
+----------+----------+--------+
| 2 | James | 15000 |
| 4 | Maddie | 12000 |
+----------+---------+---------+
3 rows in set (0.00 sec)[/c]
Key Points
- SQL EXCEPT Clause - Return all the result obtained from first SQL proclamations that are not given back by second SELECT proclamation.