SQL - SPLessons

SQL Except

Chapter 41

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

SQL Except

SQL Except

shape 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.

shape Conceptual
figure

SQL Except Syntax

shape 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>;

shape Examples

The below example describes the execution of SQL Except clause.

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)

Summary

shape Key Points

  • SQL EXCEPT Clause – Return all the result obtained from first SQL proclamations that are not given back by second SELECT proclamation.