CODEWITHSUNDEEP
sqlsql-server
RRM
RRM

Reputation: 3451

Select Except Select with extra column

Let's suppose I have a sql query:

SELECT FirstName,LastName FROM Persons1
EXCEPT
SELECT FirstName,LastName FROM Persons2

It will work if the tables have the same structure. But now I also want to have an extra column in my table - Id of person present in first table but absent in second table

SELECT PersonId,FirstName,LastName FROM Persons1
EXCEPT
SELECT FirstName,LastName FROM Persons2

won't work of course.

What would be the best way to get it? My real life case has much more columns and some values may be null.

Upvotes: 0

Views: 928

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use NOT EXISTS instead:

SELECT 
    p1.PersonId, p1.FirstName, p1.LastName
FROM Persons1 p1
WHERE NOT EXISTS(
    SELECT 1
    FROM Persons2 p2
    WHERE
        p2.FirstName = p1.FirstName
        AND p2.LastName = p2.LastName
);

This will give you rows from Persons1 whose FirstName and LastName do not exist on Persons2.

Upvotes: 3

Michael Edmison
Michael Edmison

Reputation: 873

Here is one option:

SELECT P.PersonID
    ,E.FirstName
    ,E.LastName
FROM (
    SELECT FirstName,LastName FROM Persons1
    EXCEPT
    SELECT FirstName,LastName FROM Persons2
) E
INNER JOIN Person1 P
    ON P.FirstName = E.FirstName
    AND P.LastName = E.LastName

Upvotes: 1

Related Questions