SQL Exercises: Filter records using not in operator

SQL Wildcard & Special Operator: Exercise-3 with Solution

From the following table, write a SQL query to find the details of those salespeople who live in cities other than Paris and Rome. Return salesman_id, name, city, commission.

Sample table: salesman

 salesman_id |    name    |   city   | commission 
-------------+------------+----------+------------
        5001 | James Hoog | New York |       0.15
        5002 | Nail Knite | Paris    |       0.13
        5005 | Pit Alex   | London   |       0.11
        5006 | Mc Lyon    | Paris    |       0.14
        5007 | Paul Adam  | Rome     |       0.13
        5003 | Lauson Hen | San Jose |       0.12

Sample Solution:

-- Selecting all columns from the 'salesman' table
SELECT *
-- Specifying the table to retrieve data from ('salesman')
FROM salesman
-- Filtering the results based on the condition that 'city' is not in the specified list ('Paris' and 'Rome')
WHERE city NOT IN('Paris','Rome');

or Equivalent:

-- Selecting all columns from the 'salesman' table
SELECT *
-- Specifying the table to retrieve data from ('salesman')
FROM salesman
-- Filtering the results based on the condition that 'city' is not in the list ('Paris', 'Rome')
WHERE NOT city IN ('Paris', 'Rome');

Output of the Query:

salesman_id	name		city		commission
5001		James Hoog	New York	0.15
5005		Pit Alex	London		0.11
5003		Lauson Hen	San Jose	0.12

Code Explanation:

The said SQL query that selects all columns (*) from the table called 'salesman' where the city is not in the list of values ('Paris','Rome').
It will return all rows from the "salesman" table where the city column has a value that is not 'Paris' or 'Rome'.
The NOT IN operator is used here to exclude the specified values in the list and return all other values in the column.

Relational Algebra Expression:

Relational Algebra Tree:

Explanation:

Visual presentation :

Practice Online

Query Visualization for Sample Solution:

Duration:

Rows:

Cost:

Query Visualization for alternate Sample Solution:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Filter records using in operator.
Next SQL Exercise: Sort records using in operator.