SQL – Intersect and Except ( map query and table )

Spread the love

SQL- INTERSECT – It identifies that results exists in both (all the query results) query results.
Note: Only mapped/found data will be shown as results.

SQL- EXCEPT  – it provides the data of first query result if it is not found in second query data.
Note : Only, not found data will be shown as results.

Prerequisite – Total no. of Columns And Data Types must me be matched, Else it will through an error
Error – Msg 205, Level 16, State 1, Line 1 All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

PRODUCT TABLE

S_NOPRODUCTMONTHYEARQTYSALES
1NIKEDEC2016107599
2REEBOKJAN2014903500
3FILAFEB201786000
4PUMAJAN2015165000
5NULLAPR201252500

 

SALES TABLE

PRODUCTQTYSALES
NIKE107599
REEBOK181200
FILA86000
PUMA165000
NULL52500

 

INTERSECT

SELECT PRODUCT , QTY , SALES
FROM PRODUCT

INTERSECT

SELECT PRODUCT , QTY , SALES
FROM SALES
Result

PRODUCTQTYSALES
NULL52500
FILA86000
NIKE107599
PUMA165000

 

 

EXCEPT

SELECT PRODUCT , QTY , SALES
FROM ALL_COLUMNS_BRAND

EXCEPT

SELECT PRODUCT , QTY , SALES
FROM COLUMN_FROM_BRAND

PRODUCTQTYSALES
REEBOK903500