Comparisons between groups of values with PostgreSQL and the IN operator.
In today’s post, I will demonstrate examples of PostgreSQL’s IN operator in stand-alone scenarios, along with additional uses combining IN with the NOT operator for other solutions or ideas of value matching checks against a list of values.
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, instruction and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus), PostgreSQL 9.6.2 and pgAdmin for these exercises.
Suppose you have the following table that contains the team members of the current project you are on:
This team_members table contains the columns: id, name, access_code and dept.
One day the boss approaches you “We have been getting negative feedback from our end users. I am calling a meeting this evening with all testers and management. Send an email out to all of them. The meeting is at 3.”
Ok. That is not too big of a problem since part of your job is to know every department’s access code. Handling this is a breeze using that access_code column:
Now you have a list of all members of the ‘Testing’ and ‘Management’ dept for the email:
By specifying in the WHERE clause of the query:
WHERE access_code = 1400 OR access_code = 100;
The returned results include all rows whose access_code columns have the value of either 1400 or 100.
But just as you smile for accomplishing this menial task, here comes the boss again. “Get those Database Developers on that email too. They need to be present at this meeting and figure out a solution to those query issues on the backend.”
Not terribly hard to add in the access_code for ‘DB Dev’ either to get this updated list for the email.
Using multiple OR statements with the access_code column.
You can now add the ‘DB Dev’ members to the email as well:
But there is a more efficient way to write these types of queries. Utilizing the access_codes column and the IN operator to construct a more readable and efficient query of this nature.
The IN operator’s syntax is as follows:
value IN (some_value1,some_value2,some_value3,..);
A query using the IN operator would look like this:
Supplying a list of access_code numbers to check with the IN operator.
By using the IN operator, you supply a list of values to check. A boolean result evaluates to true if the left-handed expression (the access_code column in this example) is equal to any of the values in the list.
The query returns all records from the team_members table that have the value 1400, 100, or 1250 in the access_code column. These records are identical from the previous query but in a more efficient and concise manner.
You are really starting to get the ‘big head’ and swell with pride about those PostgreSQL skills. But the boss is coming back around the corner headed your way once more. “You know. It would be better if the whole team is addressed with this problem of end-user complaints. After all, everyone involved shares in the glory. The good times and the bad. Tomorrow evening I will meet with the rest of the team. Those attending the meeting this evening are exempt. I will better get the message across by breaking the discussion down in two events. Each one targeting the specific group pertaining to those skill sets. Send an email to all those team members who will be attending tomorrow’s meeting also. ASAP.” he says as he leaves, deflating that previously swelled head of yours.
How on earth will you do that? Not to worry. You can simply grab everything not included in today’s email list. But how?
By combining the NOT operator with the IN operator you are returned those records that have an access_code value that is not present in the supplied list of values. That query would look something like:
Did that work?
Sure did.
NOT IN results to true if the left-hand expression’s result is unequal (does not match) to all of the values in the supplied list.
That was pretty easy. I know I would not want to sit through two meetings, with the boss on a rampage. Would you?
Just as you reach to power off your machine for the day, wouldn’t you know, here comes the boss. Again. “Send an email to the Sales Manager, informing him next weeks’ conference is canceled. I will update him with a new date once it has been set.”
Jeez does this guy ever relent? Uh-oh. You don’t know the Sales managers name. But you are good friends with ‘Billy’, ‘Clarence’ and ‘Robby’ who all work on the Sales team. You are also aware they are the only three salesmen in the company. How can you use that information to find the name of the remaining Sales member? Who happens to be the Sales manager?
One solution could be executing this query:
Using NOT IN and the process of elimination to return the correct record.
Specifying the Sales dept column in the WHERE clause of this query, the returned record will be from that dept only. And supplying a list of names you basically do not want, using the NOT and IN operators, the only remaining value in the name column from the ‘Sales’ dept returned is ‘Bob’, the Sales Manager.
The IN operator itself, or used in conjunction with the NOT operator is a simple yet powerful way to check for membership or presence of a value against a list of values.
Hopefully, through these simple examples, you have an idea of how to use the IN and NOT operators in some of your queries, offering options for shorter and more efficient queries, instead of checking lists of values using multiple OR statements.
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening here.
To receive notifications for the latest post from this blog via email, please subscribe by clicking the ‘Follow Digital Owl’s Prose’ button in the sidebar!
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.