Case Insensitive SQL Search for Missing Values in Another Table

Problem: 

You need to compare two tables to find values in one table that are missing in the other table.   SQL server default is case insensitive so if "Dog" is in table A and "DOG" is in table B there is a match.    

Solution:  

You need to set the COLLATE value to get your desired result of a case sensitive comparison. 

We will compare two tables,  The graniteRequested table has a 'Color' column.  The colorGranite table has a 'color' column.   

This SQL query will look for colors that are in the graniteRequested table but not in the colorGranite table. 

SELECT DISTINCT gR.Color   

  FROM graniteRequested gR

  WHERE gR.Color NOT IN 

  (SELECT colorGranite.color   Collate SQL_Latin1_General_CP1_CS_AS   FROM colorGranite)

 Collate SQL_Latin1_General_CP1_CS_AS   makes this a case sensitive search. 

Visit www.BlueCanyonSoftware.com if you need custom software development for your business.