SQL IN Archives - SQL Training Online

January 18, 2013 by Joey Blue Leave a Comment

SQL Not Like with Multiple Values

I came across a forum post where someone wanted to use SQL NOT LIKE with multiple values.

They were trying to exclude multiple values from the SQL query, but they were needing to use wildcards.

If you wanted to just filter values without wildcards, you would use the following query.

select *
from table1
where column1 not in ('value1','value2','value3');

The only problem was that they needed to compare using the LIKE operator.

It would be nice if you could just stick some wildcards in the in clause like this:

where column1 not in ('%value1%','%value2%','%value3%')

But, you can’t stick a wildcard inside of the IN clause. So, here is the easiest solution.

select *
from table1
where column1 not like '%value1%'
and column1 not like '%value2%'
and column1 not like'%value3%';

If you want to play around with the Boolean logic, you rearrange the query like this.

select *
from table1
where not (column1 like '%value1%'
or column1 like '%value2%'
or column1 like'%value3%');

SQL Not Like with Multiple Values

What happens if you don’t put those parenthesis in?

Here are a few other posts you might enjoy:

SQL Wildcards | SQL Multiple Filter | How to use the SQL In Statement with Subquery

You can visit me at any of the following:

SQL Training Online: http://www.sqltrainingonline.com

Twitter: http://www.twitter.com/sql_by_joey

Google+: https://plus.google.com/#100925239624117719658/posts

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline

October 29, 2012 by Joey Blue Leave a Comment

How to use the SQL In Statement with Subquery

Video: How to use the SQL In Statement with Subquery – SQL Training Online
In this video, I show you how to use the SQL IN Statement with Subquery in SQL Server 2012 using SQL Server Management Studio.

[sharebox4 sharetext=”Share This Page”] [/sharebox4]

How do you use the SQL IN Statement with a Subquery?

I will show you in 3 steps that build on each other:

Use the SQL OR operator.
Use the SQL IN operator.
Substitute a SQL Subquery into the IN Clause.

I will start with a simple query of the SQL Training Online Simple DB employee table where I want to retrieve 3 employee numbers: 7369,7499,7521

select * from employee
where employee_number=7369
or employee_number=7499
or employee_number=7521

Here, you can see that I used the SQL OR statement to get the 3 employees.

But, I can make this a little cleaner by using the SQL IN statement:

select * from employee
where employee_number in (7369,7499,7521)

This query returns the same data as before, but it has less typing.

So, now I want to define a subquery. It is simply:

“A subquery is a query inside of another query.”

Let’s start the example. First, I will look at the customer table and specifically, the salesman employee number:

select saleman_employee_number from customer

This query returns a list of employee numbers attached to a customer. But, I want to filter out the nulls and get rid of the repeated employee numbers. So, here is the query for that.

select distinct saleman_employee_number from customer
where saleman_employee_number is not null

Now, this is where the magic of a subquery comes in. Since this query returns a list of values, you can simply use it in the original query as the SQL IN statement. It looks like this:

select * from employee
where employee_number in (select distinct saleman_employee_number from customer where saleman_employee_number is not null)

And, that’s it. You have now used the SQL IN statement with Subquery.

Let me know what you think by commenting or sharing on twitter, facebook, google+, etc.

If you enjoy the video, please give it a like, comment, or subscribe to my channel.

You can visit me at any of the following:

SQL Training Online: http://www.sqltrainingonline.com

Twitter: http://www.twitter.com/sql_by_joey

Google+: https://plus.google.com/#100925239624117719658/posts

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline

June 10, 2012 by Joey Blue Leave a Comment

SQL IN

The SQL IN clause is used when you are filtering data and you want to look for more than one value.

Let’s take a look at the following query:

select * 
from customer 
where customer_company = 'Dell' 
 or customer_company='HP' 
 or customer_company='Walmart';

This query says that we want all of the columns, from the company table, where the customer_company is either Dell, or HP, or Walmart.

Here are the results:

This query works great, but it means you have to type a lot more than you need to.

That is where the “IN” clause comes into play. Instead of typing “customer_company” 3 times like we did in the previous SQL Query, we can write the query as follows:

select * 
from customer 
where customer_company IN ('Dell','HP','Walmart');

The results of this query are the exact same as the results above.

Notice how much less typing I had to do. And, notice the syntax.

It goes column name (in this case customer_company), then you use the “IN” word, then you list all of the values in side of parenthesis and separated by commas.

That’s it. I often use the IN clause instead of a bunch of OR’s, because it is neater and easier for me to read.

As you start putting more complex queries together, it will be a lot easier to keep track of an IN clause instead of a bunch of OR statements.

Remember to put any question, comments, or other topics you would like for me to explain into the comment section below.