Lec-66: EXIST and NOT EXIST Subqueries(part-8) | Database Management System

TL;DR

Learn how to use Exist and Not Exist keywords in correlated nested queries in SQL.

Transcript

Hello friends! Welcome to Gate Smashers So the topic is Exist and Not Exists In SQL Exist and Not Exist These keywords we use in correlated nested query We correlated nested query In this we use these both keywords Exist and Not Exist Why do we use? Because, I have already explained nested queries Over there what did we talk? That what is nested... Read More

Key Insights

• 🤨 Exist and Not Exist keywords are used in correlated nested queries to check for the existence of a row in the inner table corresponding to the outer row.
• 💨 Exist and Not Exist keywords execute in a different way compared to the In and Not In keywords.
• ↩️ Exist and Not Exist keywords return true or false based on the existence of a row in the inner table.
• 👻 Correlated nested queries allow us to join the outer and inner queries using keywords like Exist and Not Exist.
• 🤨 Exist and Not Exist keywords work with a top-down approach, checking each row of the outer query against the inner table.
• 🤨 The output of Exist and Not Exist keywords determines if the corresponding row from the outer query is included in the final result.
• ⚾ Exist and Not Exist keywords are useful when we want to filter the results of a query based on the existence of related data in another table.

Questions & Answers

Q: What are Exist and Not Exist keywords used for in SQL?

Exist and Not Exist keywords are used to relate the outer and inner queries in correlated nested queries, allowing us to check if a row exists in the inner table corresponding to the outer row.

Q: How does Exist and Not Exist execution differ from In and Not In keywords in SQL?

Exist and Not Exist keywords execute in a top-down approach, checking each row of the outer query and comparing it with the inner table. In contrast, In and Not In keywords compare the output of the inner query with the outer query as a whole.

Q: How do we use Exist keyword in a correlated nested query?

To use Exist keyword, we select * from the outer table and include the condition "Where [OuterTable].[Column] Exist(select [Column] from [InnerTable] where [OuterTable].[Column]=[InnerTable].[Column])".

Q: How do we use Not Exist keyword in a correlated nested query?

To use Not Exist keyword, we select * from the outer table and include the condition "Where [OuterTable].[Column] Not Exist(select [Column] from [InnerTable] where [OuterTable].[Column]=[InnerTable].[Column])".

Summary & Key Takeaways

• Exist and Not Exist keywords are used in correlated nested queries to relate the outer and inner queries in SQL.

• Exist and Not Exist keywords execute in a different way compared to the In and Not In keywords.

• Exist and Not Exist keywords return true or false based on whether a row exists in the inner table corresponding to the outer row.