Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have to generate a query output for not matching records.

Assuming that i have two tables Table1 and Table2. Both Tables have the columns as DrugName. No Id column. Just the value column.

I have to generate an output from Table1 by filtering DrugNames which are in Table2. Assuming, Table1 has 1000 records and Table2 has 100 records. On applying filter, i should get 900 records from Table1.

select * from Table1
left join Table2 on
Table1.DrugName = Table2.DrugName
where Table2.DrugName is null  

Above syntax returns non matching records.

Let's say in Table2.DrugName will be "ABCD" while in Table1.DrugName will be either "XYZ ABCD" or "ABCD GHIJ". If Table1.DrugName has matching word "%ABCD%", I have to filter.

share|improve this question
It looks like the relationship between those two tables is foggy at best. How flexible is the current structure? – Thomas Stringer Mar 7 '14 at 22:52
How did this question get upvoted?! The requirements are so unclear – Phil Mar 8 '14 at 1:55
Can you provide sample output? Joining on something that "doesn't match" is kind of meaningless. – NReilingh Mar 8 '14 at 3:28
up vote 6 down vote accepted
Select * from Table1
Left Join Table2
on Table1.DrugName LIKE '%' + Table2.DrugName + '%' 

...but bear in mind that performance could be awful. You might be better off splitting the longer names into parts, and then joining on the DrugNameElements table. That way, you can index the elements and quickly identify the matching ones.

share|improve this answer
isn't this "like" will return matching records? I guess OP is looking for non matching records. – Anup Shah Mar 8 '14 at 3:06
I took it to mean inexact matches, rather than complete non-matches. He describes having VICKS in one and ACTION VICKS 500 in the other. – Rob Farley Mar 8 '14 at 13:58
@Robfarley, Thanks. That query returns matching record right. Though, havent tried. I am looking for NON Matching records. My join syntax could be a wrong approach. I can use either NOT EXIST or ROW_NUMBER PARTITION OVER BY something like that – goofyui Mar 9 '14 at 20:50
If Rob's query gives you the matching rows and you want the rest (non-matching rows), add WHERE Table2.DrugName IS NULL; – ypercubeᵀᴹ Mar 9 '14 at 21:19
@goofyui, so my comment is right. Farley's query gives the records for matching rows. You can use same query by altering "LIKE" operator to "NOT LIKE" operator. – Anup Shah Mar 10 '14 at 3:56

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.