Test If Rows Exist In Subquery

Create Table

-- Create table called adventurers
CREATE TABLE adventurers (
    -- string variable
    name varchar(255),
    -- integer variable
    age int,
    -- string variable
    race varchar(255),
    -- string variable
    weapon varchar(255)
)

Insert Rows

-- Insert into the table adventurers
INSERT INTO adventurers (name, age, race, weapon)
VALUES ('Fjoak Doom-Wife', 28, 'Human', 'Axe'),
       ('Alooneric Cortte', 29, 'Elf', 'Bow'),
       ('Piperel Ramsay', 35, 'Elf', 'Sword'),
       ('Casimir Yardley', 14, 'Elf', 'Magic')

Test If Rows Exist In Subquery

-- Retrieve all the rows in adventurers
SELECT * FROM adventurers
-- Where rows exist in
WHERE EXISTS
-- A subquery that will
(
    -- Select all the names in adventurers
    SELECT name FROM adventurers
    -- Where race is elf
    WHERE race = 'Elf'
)
nameagerace
Fjoak Doom-Wife28Human
Alooneric Cortte29Elf
Piperel Ramsay35Elf
Casimir Yardley14Elf

Test If Rows Do Not Exist In Subquery

-- Retrieve all the rows in adventurers
SELECT * FROM adventurers
-- Where the name of the adventurer is in
WHERE NOT EXISTS
-- A subquery that will
(
    -- Select all the names in adventurers
    SELECT name FROM adventurers
    -- Where race is dwarf
    WHERE race = 'Dwarf'
)
nameagerace
Fjoak Doom-Wife28Human
Alooneric Cortte29Elf
Piperel Ramsay35Elf
Casimir Yardley14Elf