Alan Alan - 1 year ago 43
SQL Question

How can I check that an entry in one column of a tables appears at least once in another column?

I have a table of words that looks like this?

CREATE TABLE [dbo].[WordForm] (
[Word1] VARCHAR (20) NOT NULL,

Here's what the data looks like?

Word1 Word2
----- -----
abandon abandon
abandon abandonment
adapt adapt
adapt adaptable
adapt adaptation
adapt adapter
adversely adverse
adversely adversely
adversely adversity

How can I check that every word which appears in column Word1 is present in Word2 at least once and also if there are words that appears in Word1 but not Word2 I need to know what the words are.

Answer Source

For a more performant solution on large datasets try this:

SELECT w.Word1 as [Word 1 Not in Word 2]
FROM   WordForm w
SELECT w.Word2
FROM   WordForm w

SELECT w.Word2 as [Word 2 Not in Word 1]
FROM   WordForm w
SELECT w.Word1
FROM   WordForm w