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.

Press + to interact

-- 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;

Access this course and 1400+ top-rated courses and projects.

Preview Free Lessons→

Preview Free Lessons

Introduction

Basic SQL

Joins

Nested Queries

Multi Table Operations

Views

Stored Procedures

Triggers

Miscellaneous

Practice & Prep

Epilogue

Nested Row Queries

Nested Row Queries