Why do we use a sub-queries in SQL?

Question

I don't understand we would need to use sub-queries in data analysis or why the order matters. Can someone explain it to me?

Example: Why do the 2 codes below produce 2 different outputs from the difference in ordering of the code? They are both trying to find the lowest rental price in 2006.

Code 1:

SELECT
     film_id
    ,title
FROM Films
WHERE release_year = 2006 
AND rental_rate =
(
    SELECT
        MIN(rental_rate)
    FROM Films
);

Code 2:

SELECT 
     film_id
    ,title
FROM Films
WHERE rental_rate = 
(
    SELECT
        MIN(rental_rate)
    FROM Films
    WHERE release_year = 2006
);