Find all companies located in every city SQL

Question

Question: Assume that the companies may be located in several cities. Find all companies located in every city in which "Small Bank Corporation" is located.

Answer:

Select S.company_name
from company S
where not exists
  (select city
  from company 
  where company_name = 'Small Bank Corporation'
  except
    (select city
    from company T
    where S.company_name = T.company_name
    )
 )

How I am reading this: Select all companies where there doesn't exist cities of 'Small Bank Corporation' that are not located in table S and T).

However, S.company_name will always = T.company_name because they are duplicates of the same table?

I am asking for help understanding the solution.

Any explanation appreciated!

Dmihawk Dmihawk · Accepted Answer · 2017-02-21T01:24:48

This takes a bit of dissection, so let's break it down by individual parts.

To make it easier, think of the query as running over every row in the Company table, one by one.

Part 1:

SELECT s.Company_Name FROM Company s

This sets our current company. So let's say the first row in the Company table is for Apple, s.Company_Name is now 'Apple'.

Part 2:

SELECT City FROM Company WHERE Company_Name = 'Small Bank Corporation'

This gives us a list of every city where the Small Bank Corporation exists, we're going to need this shortly.

Part 3:

SELECT City FROM Company t WHERE s.Company_Name = t.Company_Name

Remember how s.Company_Name was 'Apple'? This is going to give us a list of every city where Apple exists.

Part 4:

Now that we have our lists from Part 2 and Part 3, we use the EXCEPT clause to "remove" all the entries in Part 3 from the results in Part 2.

Example 1 - Say in Part 2 we got "London" and "New York City" (the cities where Small Bank Corporation exist) and in Part 3 we got "London", "New York City" and "Paris" (the cities where Apple exist). In this example, the result of our EXCEPT clause is an empty result set (a SELECT that returns no rows).

Example 2 - However, if the Part 2 had given us "London", "New York City" and "Paris" and Part 3 had given us "London" and "New York City", then the result of our EXCEPT clause would be a single row, containing "Paris".

This bring us to...

Part 5:

WHERE NOT EXISTS(...)

Which will remove any companies which don't exist in every city where 'Small Bank Corporation' do. If they were Example 1 (from Part 4), then the NOT EXISTS is true, but if they were Example 2, then the NOT EXISTS is false and so that company is not returned.

It takes a while to get your head around, but I hope this helps at least. Let me know if anything doesn't make sense.

Find all companies located in every city SQL

2 Answers