Nesting queries in SQL

Question

The goal of my query is to return the country name and its head of state if it's headofstate has a name starting with A, and the capital of the country has greater than 100,000 people utilizing a nested query.

Here is my query:

SELECT country.name as country, 
       (SELECT country.headofstate 
        from country 
        where country.headofstate like 'A%')      
from country, city 
where city.population > 100000;

I've tried reversing it, placing it in the where clause etc. I don't get nested queries. I'm just getting errors back, like "subquery returns more than one row" and such. If someone could help me out with how to order it, and explain why it needs to be a certain way, that'd be great.

score 16 · Accepted Answer · answered Sep 17 '12 at 21:46

16

If it has to be "nested", this would be one way, to get your job done:

SELECT o.name AS country, o.headofstate 
FROM   country o
WHERE  o.headofstate like 'A%'
AND   (
    SELECT i.population
    FROM   city i
    WHERE  i.id = o.capital
    ) > 100000

A JOIN would be more efficient than a correlated subquery, though. Can it be, that who ever gave you that task is not up to speed himself?

answered Sep 17 '12 at 21:46

Erwin Brandstetter

605,456
145
1,078
1,228

If i use this kind of sql Query then it is showing me the error as below. Operand should contain 1 column(s) Can you Help me.... My Sql Query is SELECT s.* FROM j5749_faculty_subjects as s WHERE s.subjectid='1' AND ( SELECT a.* FROM j5749_facultyavailablity as a WHERE a.facultyid = s.facultyid AND a.timeid='3' ) – Amit Feb 25 '14 at 05:23
@amit: See the last part in my query: `> 100000`. You have no equivalent. You probably want `EXISTS`. [See this example.](http://stackoverflow.com/questions/14251180/find-records-where-join-doesnt-exist/14260510#14260510) Else, start a *new question* with all the details. – Erwin Brandstetter Feb 25 '14 at 15:32
Neat, looks like you can do it in the `FROM` clause too! -- just nest a query inside of parenthesis, and name it -- then you can reference it like a table from your `SELECT`, `WHERE` and `JOIN` clauses, etc. (e.g. `SELECT s.* FROM (SELECT [blah] FROM [blaz] WHERE [foo]) AS s WHERE s.whatever = 'foop'`.) – BrainSlugs83 Oct 26 '18 at 20:19

score 8 · Answer 2 · answered Sep 17 '12 at 21:30

8

You need to join the two tables and then filter the result in where clause:

SELECT country.name as country, country.headofstate 
from country
inner join city on city.id = country.capital
where city.population > 100000
and country.headofstate like 'A%'

answered Sep 17 '12 at 21:30

Adriano Carneiro

57,693
12
90
123

Thanks for your help again! And although this works perfectly, and makes sense, I am supposed to be using nested queries. Do you have an alternative? – ZAX Sep 17 '12 at 21:37
2

@ZAX if you need a nested query, take a look at my answer ... :-) – aleroot Sep 17 '12 at 21:38
3

@ZAX Dare I ask why, on earth, are you **suppposed** to use nested queries? Is this homework? Because nested queries are not even the best solution for this problem – Adriano Carneiro Sep 17 '12 at 21:38
I"m reading an sql book. I don't think I"m at that point of using inner joins yet necessarily although I have some background and understand what you're doing with the query. I guess its trying to "bring me along" if you will. – ZAX Sep 17 '12 at 21:44
1

@ZAX `Inner joins` are just explicit joins. If you need to query for data that are in 2 different tables, the best option (in performance and readability) is to join, regardless whether implicitly or explicitly – Adriano Carneiro Sep 17 '12 at 21:47

score 2 · Answer 3 · answered Sep 17 '12 at 21:46

The way I see it, the only place for a nested query would be in the WHERE clause, so e.g.

SELECT country.name, country.headofstate
FROM country 
WHERE country.headofstate LIKE 'A%' AND 
country.id in (SELECT country_id FROM city WHERE population > 100000)

Apart from that, I have to agree with Adrian on: why the heck should you use nested queries?

score 1 · Answer 4 · edited Jun 25 '14 at 12:32

1

Query below should help you achieve what you want.

select scountry, headofstate from data 
where data.scountry like 'a%'and ttlppl>=100000

edited Jun 25 '14 at 12:32

Matas Vaitkevicius

58,075
31
238
265

answered Jun 25 '14 at 12:00

user3585283

11
1

Nesting queries in SQL

4 Answers4

Linked