Aim-Use of nested qurries in SQL.
Theory-
What is a Nested Query in SQL?
A nested query in SQL contains a query inside another query. The outer query will
use the result of the inner query. For instance, a nested query can have
two SELECT statements, one on the inner query and the other on the outer query.
Tables
Employees
id name salary role
1 Augustine Hammond 10000 Developer
2 Perice Mundford 10000 Manager
3 Cassy Delafoy 30000 Developer
4 Garwood Saffen 40000 Manager
5 Faydra Beaves 50000 Developer
Awards
id employee_id award_date
1 1 2022-04-01
2 3 2022-05-01
1. In operator-The IN operator checks if a column value in the outer query's
result is present in the inner query's result. The final result will have rows
that satisfy the IN condition.
Syntax-
Output-
id name
1 Augustine Hammond
3 Cassy Delafoy
2. NOT IN operator-The NOT IN operator checks if a column value in the
outer query's result is not present in the inner query's result. The final result
will have rows that satisfy the NOT IN condition.
Syntax-
Output-
id name
2 Perice Mundford
4 Garwood Saffen
5 Faydra Beaves
3. All operator-The ALL operator compares a value of the outer query's result
with all the values of the inner query's result and returns the row if it
matches all the values.
Syntax-
Output-
id name salary role
5 Faydra Beaves 50000 Developer
4. Any operator -The ANY operator compares a value of the outer query's
result with all the inner query's result values and returns the row if there is a
match with any value.
Syntax-
Output-
id name salary role
5 Faydra Beaves 50000 Developer
3 Cassy Delafoy 30000 Developer
5. EXISTS operator -This operator checks whether a subquery returns any
row. If it returns at least one row. EXISTS operator returns true, and the
outer query continues to execute. If the subquery returns no row, the
EXISTS operator returns false, and the outer query stops execution.
Table student details
Table books
Suppose, we want to fetch the first name and the last name of those students who have issued at least
one book.
The query for this is:
6. NOT EXISTS operator - SQL NOT EXISTS is just the opposite of
the EXISTS operator and is satisfied in case no rows are returned by the
subquery. SQL NOT EXISTS is just the opposite of the EXISTS operator
and is satisfied if the result of the subquery is empty.
Syntax:
SELECT first_name, last_name
FROM student_details
WHERE NOT EXISTS (SELECT
FROM books
WHERE student details.book_id_issued = books.book_id);
Output:
First_name Last_name
Ishika Goel
mahi jain
