INTERSECT between two pseudo (derived) tables in Microsoft SQL server-CodePudding

I'm trying to find out whether there are any differences between two pseudo tables with one column (see if every element in one table is in the other)

Code:

FROM
    (SELECT Orders.OrderID
    FROM (((Categories FULL OUTER JOIN Products on Categories.CategoryID = Products.CategoryID)
    FULL OUTER JOIN  OrderDetails on OrderDetails.ProductID = Products.ProductID)
            FULL OUTER JOIN  Orders on Orders.OrderID = OrderDetails.OrderID)
            FULL OUTER JOIN  Suppliers on Suppliers.SupplierID = Products.SupplierID
    WHERE CategoryName LIKE 'Dairy%' AND country ='France')
INTERSECT

    (SELECT Orders.OrderID
    FROM (((Categories LEFT JOIN Products on Categories.CategoryID = Products.CategoryID)
    LEFT JOIN OrderDetails on OrderDetails.ProductID = Products.ProductID)
            LEFT JOIN Orders on Orders.OrderID = OrderDetails.OrderID)
            LEFT JOIN Suppliers on Suppliers.SupplierID = Products.SupplierID
    WHERE CategoryName LIKE 'Dairy%' AND country ='France')

eg of output:

Top part of query gives for first three results:

OrderID
10255
10267
10275

Second part of code (below INTERSECT) gives:

OrderID
10255
10267
10275

Now for some reason the code is not running with the intersection. The code works separately to give me the individual columns for the sub query above and below the INSERSECT, but in its entirety it just doesn't work. Error message I get is:

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'INTERSECT'.

Completion time: 2022-05-23T14:10:06.3560367 01:00

Any idea how to fix this? If it helps, the INTERSECT is underlined red.

Thanks.

CodePudding user response：

Just remove the FROM and the outer parenthesis, like this:

    SELECT Orders.OrderID
    FROM (((Categories FULL OUTER JOIN Products on Categories.CategoryID = Products.CategoryID)
    FULL OUTER JOIN  OrderDetails on OrderDetails.ProductID = Products.ProductID)
            FULL OUTER JOIN  Orders on Orders.OrderID = OrderDetails.OrderID)
            FULL OUTER JOIN  Suppliers on Suppliers.SupplierID = Products.SupplierID
    WHERE CategoryName LIKE 'Dairy%' AND country ='France'
INTERSECT
    SELECT Orders.OrderID
    FROM (((Categories LEFT JOIN Products on Categories.CategoryID = Products.CategoryID)
    LEFT JOIN OrderDetails on OrderDetails.ProductID = Products.ProductID)
            LEFT JOIN Orders on Orders.OrderID = OrderDetails.OrderID)
            LEFT JOIN Suppliers on Suppliers.SupplierID = Products.SupplierID
    WHERE CategoryName LIKE 'Dairy%' AND country ='France'

CodePudding user response：

The issue is that you have a FROM Clause attempting to run 2 sub selects enclosed within parenthesis.

SELECT 
    *
FROM
    (SELECT 1 AS a)

Yields an error:

Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ')'.

Adding an alias, however, works fine:

SELECT 
    *
FROM
    (SELECT 1 AS a) as a

Update:

You only have to alias the first query:

SELECT 
    *
FROM
    (SELECT 1 AS a) as a
INTERSECT
    (SELECT 1 AS a)

Or, as suggested by RBarryYoung, remove the parenthesis altogether.