Introduction
1. Introduction

Instruction

Hello and welcome to the fourth part of our SQL Practice Set! Today we'll work with subqueries! Here's a brief reminder:

We can use subqueries in the WHERE clause to compare a given column with the result of a whole query. When comparing with the result of the subquery, you can use conditional operators by themselves:

SELECT cat_id
FROM cats
WHERE age > (SELECT age FROM cats WHERE cat_name = 'Kitty')

or conditional operators with the ANY or ALL keywords, if your subquery can return multiple rows:

SELECT cat_id
FROM cats
WHERE age > ANY (SELECT age FROM cats WHERE cat_name = 'Kitty')

or the operator IN, if the value of the column compared with the subquery has to be in the result of particular subquery, e.g.

SELECT cat_id
FROM cats
WHERE age IN (SELECT age FROM cats WHERE cat_name LIKE 'K%'

Correlated subqueries refer to the outer query. Take a look:

SELECT cat_id
FROM cats c
WHERE cat_id IN (SELECT owned_cat_id FROM owner WHERE wage > 5000 and owned_cat_id = c.cat_id)

In the above query we selected the IDs of cats that belong to owners who earn more than $5000. Note that the subquery refers to the table cats in the outer query.

We can also use the subqueries in the FROM clause, and filter our rows in this way. The subquery in the FROM clause has to have an alias.

It is also possible to have a subquery in the SELECT clause. Such a subquery has to return exactly one row and column. Here's an example:

SELECT 
   name, 
   (SELECT avg(age) FROM cats c2 WHERE c2.name = c1.name)
FROM cats c1

Exercise

Click the Next exercise button to continue.

Console

Code editor

Result

TableConsole