Comparing Two Columns Using SQL to Make Sure the Same Set of Values Are Present in Both

How do I compare two columns for equality in SQL Server?

What's wrong with CASE for this? In order to see the result, you'll need at least a byte, and that's what you get with a single character.

CASE WHEN COLUMN1 = COLUMN2 THEN '1' ELSE '0' END AS MyDesiredResult

should work fine, and for all intents and purposes accomplishes the same thing as using a bit field.

Redshift SQL: Check if same set of values are IN and NOT IN two different columns of table

"WHERE NOT IN" can create inefficient executions but this is so simple that I hope the Redshift query compiler should optimize this code. If the "IN" list gets long this can get very slow. Generally "WHERE NOT EXIST" is more efficient than "WHERE NOT IN" but again in this simple case it should be noticeable.

For something this simple (2 values) you can try -

buyers in ('EXAMPLE 1','EXAMPLE 2') and sellers <> 'EXAMPLE 1' and sellers <> 'EXAMPLE 2'

and see if there is any speed up. I hope this make no difference.

Checking multiple columns for one value

You can use the IN predicate, like so:

SELECT * FROM table WHERE 123 IN(col1, col2, col3, col4);

SQL Fiddle Demo

---

it's the opposite version of IN.

No it is not, It is the same as using the ORs the way you did in your question.

---

To clarify this:

The predicate IN or set membership is defined as¹:

Where the Value Expression can be either ²:

So it is fine to do it this way, using the value expression 123, which is a literal.

---

^{1, 2: Images from: SQL Queries for Mere Mortals(R): A Hands-On Guide to Data Manipulation in SQL}

SQL WHERE.. IN clause multiple columns

You can make a derived table from the subquery, and join table1 to this derived table:

select * from table1 LEFT JOIN 
(
   Select CM_PLAN_ID, Individual_ID
   From CRM_VCM_CURRENT_LEAD_STATUS
   Where Lead_Key = :_Lead_Key
) table2
ON 
   table1.CM_PLAN_ID=table2.CM_PLAN_ID
   AND table1.Individual=table2.Individual
WHERE table2.CM_PLAN_ID IS NOT NULL

---

Related Topics