← Home

SQL - `IN` operator with combination of columns

If you write SQL queries often, you should have probably used the IN operator which determines if a specified value matches any value in a list or a subquery. Below is the most simple use-case with single value, for examle:


SELECT	* 
FROM 	`students`
WHERE 	id NOT IN (1,5,6,3)

But one thing that you might not know is that you can use that for a combination of values as well. For example lets take a table where there are student names and subjects

Students Table

And there are some user and subject combinations that we do not want, lets say below are the ones that we do not want:

Not Required Columns

In that case, our SQL would look like the following:


SELECT * 
FROM   students 
WHERE 
	( name, subject )
NOT IN 
	(
		('Jane Doe', 'Analysis of Algorithms'),
		('John Doe', 'Software Engineering - II'),
		('Jane Doe', 'Professional Practices')
	);

The list used in NOT IN might be provided in the form of list i.e. the way it is provided in the above example or it might be returned from some subquery.

Liked it? Follow me on twitter@kamranahmedseandtweet about it

Subscribe yourself

You can subscribe to my blog posts by providing your email below. Your email will be kept safe and there will be no spamming ..Promise!