Nguyen Hoa Nguyen Hoa - 9 months ago 30
MySQL Question

SQL Subquery Exercises: Display the salesmen which name are alphabetically lower than the name of the customers

This is the question on w3resource:
http://www.w3resource.com/sql-exercises/subqueries/sql-subqueries-inventory-exercise-21.php


You can see the content of the table on the link above.
This is my solution:


SELECT *
FROM SALESMAN S WHERE S.SALESMAN_ID IN
(
SELECT C.SALESMAN_ID
FROM CUSTOMER C
WHERE S.NAME < C.CUST_NAME
);


And I recieved only one salemen named Jame Hoog.
This is the website's solution:

SELECT *
FROM salesman a
WHERE EXISTS
(SELECT *
FROM CUSTOMER b
WHERE a.name < b.cust_name);


I don't really understand why do they use EXISTS and I think my solution logically is still the same as their solution. Please tell me what's wrong with my solution. Please

Answer Source

The two queries are not the same, as I will explain. There is nothing wrong with your query or your single record result set, as this demo shows:

Rextester

This query is finding every salesman which has at least one customer whose name is lexicographically greater than his own. Only salesman James Hoog has a name which is lexicographically less than any of his customers.

On the other hand, the query from the website is looking for salesman names which are lexicographically less than any customer (either his own or someone else's) from each record in the Salesman table. This is a much more lax requirement than what is used in your query. The logic here is to retain any salesman for which there exist any customers whose name is greater than that of the salesman. Only Pit Alex and Paul Adam fail to meet this requirement, there being no customers whose last name begin with the letter 'P' or any letter greater than that.