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
And there are some user and subject combinations that we do not want, lets say below are the ones that we do not want:
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.
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!