Now we have the tables referencing each other- but we still need a way to query and that information.

We'll need a way to know some unique information about the User, like their email, and be able to find all of the Posts for that user.

We can do this with Subqueries.

Subqueries

In SQL, a subquery is a query nested inside another query, usually within the WHERE, FROM, or SELECT clause. It allows you to perform complex operations by using the results of one query as input to another query. Subqueries are sometimes also called or .

Subqueries let us use the results of one query to help us build another query.

In this example let's say we want to find the posts of the user with the email .

We can first write our statement to find the posts:

SELECT * FROM Posts WHERE userId = "mario's id?"

But since we don't know the userId, we'll replace everything after with a subquery, or another query that will grab the ID of the user for us:

SELECT * FROM Posts WHERE userId =
(SELECT id FROM Users WHERE email="mario@email.com");

You can think of this a bit like how the order of operations in math works. Our will resolve first, giving us this:

SELECT * FROM Posts WHERE userId =
("2");

The IN operator

Subqueries are a very powerful tool. Combining subqueries with the operator will let you query for related data in tables and get results from multiple relations.

In the previous example- we just returned one id from our subquery. But what if we wanted to return multiple users? What if we need to grab all the posts from all the users that match some criteria? Well, we'd use the operator instead of .

For example- we can grab all the posts from Users who's email ends in by combining what we previously

SELECT * FROM Posts WHERE userId IN
(SELECT id FROM Users WHERE email LIKE "%email.com");

This will resolve the subquery first, just like before:

SELECT * FROM Posts WHERE userId IN
("1","2","3");

And then grab all of the Posts from user's with the ID of 1,2, or 3.

See if you can build your own subqueries to grab specific posts.

Try to build subqueries that retrieve all the posts:

• From users age 30 or older
• From users who's name contains 'ar' anywhere in their name (HINT: Check the 'Like' Lesson)

0 Comments

Top comments

"Please login to view comments"

Join the Conversation!

Subscribing gives you access to the comments so you can share your ideas, ask questions, and connect with others.

Upgrade your account