When you use a LEFT JOIN with the condition s1.price < s2.price, it means that for each row in s1, MySQL will try to find all the rows in s2 where the price is lower than the price in s1.
If there is no such row in s2, then all the columns of s2 will be NULL for that particular row of s1. This happens because LEFT JOIN returns all the rows from the left table (s1 in this case) along with any matching rows from the right table (s2).
However, if there is a matching row in s2, then MySQL will set the values of the columns in s2 for that particular row based on the values in the matching row. In this case, s2.article would have a non-NULL value.
So in the context of the query:
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;
the condition s2.article IS NULL is used to filter out the rows where there is a matching row in s2, i.e., where s1 is not the row with the lowest price for that article. By checking if s2.article is NULL, we can determine whether s1 has the minimum price for that article or not.
