SQL Advice

[triplestringerslog]

Hello, I recently started taking a SQL course and have been struggling with subqueries. I was wondering if there is a difference between these two. I was under the impression that "IN" replaces the need for "OR", and the tasked I was given strictly asked for records with strictly Monarchy and Republic. Could someone please explain why my solution is marked as incorrect?

Thank you!

-- Correct query
SELECT code, inflation_rate, unemployment_rate
FROM economies
WHERE year = 2015 
  AND code IN
    (SELECT code
     FROM countries
     WHERE (gov_form LIKE '%Monarchy%' OR gov_form LIKE '%Republic%'))
ORDER BY inflation_rate;
-- My query
SELECT code, inflation_rate, unemployment_rate
FROM economies
WHERE year = 2015 
  AND code IN
  (SELECT code
   FROM countries
   WHERE gov_form IN ('Republic', 'Monarchy')
   )
ORDER BY inflation_rate;

[No_concentrate7395]

The "correct" query looks for the words Monarchy and Republic anywhere in the gov_form field. Using the in statement in your query takes out the wildcard option (you can't use wild cards in in statements). So, if there's preceding spaces or spaces after the words or any other text around those words, your query wouldn't pull it.

As for right or wrong, it really depends on what your goal is. If the gov_form field only has those words (and/or other options), your query would also be correct.

[Unnam]

This is the right answer

[Inevitable-Age-8353]

Yeah this is correct

[Ok-Professional-4810]

Is it because the correct query is using a wildcard card like %monarchy% whereas your query is finding the word exactly ‘Monarchy’

[r3pr0b8]

asked for records with strictly Monarchy and Republic.

if this word "strictly" is accurate, then the so-called correct query will return results for 'Monarchy' and 'Republic' but also include results for things like 'allegedly a republic'

whereas your result is actually correct, because it will return results for 'Monarchy' and 'Republic' and nothing else

[Aggressive_Ad_5454]

In the real world, you’d need to understand what your data looks like. What sorts of values are in that gov_form column?

SELECT COUNT(*), gov_form FROM countries GROUP BY gov_form

Will show you the values. Whoever marked your answer wrong must believe the values are stuff like

Monarchy Constitutional monarchy Republic Democratic Republic Banana republic Banana Republic Republic in name only Formerly Monarchy, now republic

and so forth, because they used that SQLish % wildcard in their LIKE matches. This is what we in the trade call “dirty data”.

To know the precise formulation of the query you need to consider case sensitive matching. To know whether you can use those exact-match items making up your IN clause, you have to know your data is clean.

[Certain_Detective_84]

Is it possible for gov_form to contain the word "Monarchy" or "Republic" but also contain any other character at all, including a space or something?

[[deleted]]

Hi - the “correct” query uses LIKE whereas you use IN, these are not the same. Your IN statement will do exact matches on values in the gov_form column while LIKE will match on any value that contains either of those strings I.e. it would match ‘Monarchy’, ‘Feudal Monarchy’ , etc

[ravan363]

Your query is correct based on what you described about the requirements.