Fast Searches with INTERSECT and EXCEPTION Operators

     

      INTERSECT and EXCEPTION operators are the most basic operators to filter two query results. They return distinct rows by comparing two query results.

• EXCEPT returns distinct rows from the left input query that aren't output by the right input query. It works like OUTER JOIN.
• INTERSECT returns distinct rows that are exist in left and right query results. It works like INNER JOIN

The number and the order of the columns must be same in both queries, also the data types must be compatible to use INTERSECT and EXCEPTION in your queries.

In my experience, I found both operators very useful and pretty fast when I am dealing with normalized tables.  Let's say we have 3 big tables which contain information about the products and product locations.Let's say users want to search these tables and expecting to see basic results with every change in UI.

 Following query gives us pretty fast results, It might not display the information from ProductLocation and ProductDetails since we are not using any JOIN operators but it can handle to search tables every time user presses a key on the keyboard or change a drop down  in UI.