Search⌘ K
Home
Courses
An Introductory Guide to SQL

Nested Row Queries

Explore how to use nested row queries in SQL to match multiple columns between tables. Understand applying nested queries with WHERE and FROM clauses to solve practical data retrieval problems involving multi-column conditions and derived tables.

We'll cover the following...

Nested Row Queries

In this lesson we’ll study nested queries that return rows, allowing the outer query to match on multiple different column values. Furthermore, so far, we have used nested queries only with the WHERE clause, but now we’ll also use them with the FROM clause.

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/32lesson.sh and wait for the MySQL prompt to start-up.

MySQL
-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 



-- Query 1

SELECT FirstName

FROM Actors

INNER JOIN DigitalAssets

ON Id=ActorId 

AND MONTH(DoB) = MONTH(LastUpdatedOn) 

AND DAY(DoB) = DAY(LastUpdatedOn);



-- Query 2

SELECT FirstName

FROM Actors 

WHERE (Id, MONTH(DoB), DAY(DoB))

IN ( SELECT ActorId, MONTH(LastUpdatedOn), DAY(LastUpdatedOn)

     FROM DigitalAssets);



--Query 3

SELECT ActorId, AssetType, LastUpdatedOn FROM DigitalAssets;



-- Query 4

SELECT FirstName, AssetType, LastUpdatedOn 

FROM Actors  

INNER JOIN (SELECT ActorId, AssetType, LastUpdatedOn 

            FROM DigitalAssets) AS tbl 

ON ActorId = Id;



-- Query 5

SELECT FirstName, AssetType, LastUpdatedOn 

FROM Actors 

INNER JOIN (SELECT ActorId, AssetType, LastUpdatedOn 

            FROM DigitalAssets) AS tbl 

ON ActorId = Id

WHERE FirstName = "Kim";



-- Query 6

SELECT FirstName, AssetType, LastUpdatedOn 

FROM Actors 

INNER JOIN (SELECT ActorId, AssetType, LastUpdatedOn 

            FROM DigitalAssets) AS tbl 

ON ActorId = Id

WHERE FirstName = "Kim"

ORDER BY LastUpdatedOn DESC LIMIT 1;
Terminal 1
Terminal
Loading...

  1. Let’s say we want to find the list of all the actors whose latest update to any of their online accounts was on the day of their birthday. The date of birth for each actor is in the Actors table and the LastUpdatedOn column is in the DigitalAssets table. We can extract the birthday month and day using the MONTH() and DAY() functions on the DoB column and match them with ...