SQL EXISTS and NOT EXISTS

Overview

This lecture explains how to use the SQL keywords EXISTS and NOT EXISTS to filter query results based on the presence or absence of related records in a database.

Conditional Query Results

• Sometimes queries need to return results only if certain related records exist in other tables.
• Example: List salespeople only if they have sales recorded in the orders table.
• Example: List employees only if they submitted timesheets for a given period.

Using EXISTS in SQL

• The EXISTS keyword in SQL checks if a subquery returns any records for each row.
• EXISTS is evaluated as either TRUE or FALSE.
• If TRUE, the main query includes that row in the result.
• Example: To find passengers who are frequent flyer members, use EXISTS with a subquery checking the frequent flyer table.

Example Query Explanation

• The query selects passenger names and their number of flights if they are in the frequent flyer club.
• The EXISTS subquery matches frequent flyer numbers between the passenger and frequent flyer tables.
• Only passengers with a match are included in the result.

Using NOT EXISTS

• NOT EXISTS returns TRUE if the subquery finds no matching records.
• Use NOT EXISTS to find passengers who are not frequent flyer members.
• This is useful for targeting non-members, for example, in marketing.

Practical Example Results

• Running the NOT EXISTS query lists passengers who have flown but are not in the frequent flyer program.
• The result shows names and number of flights for non-members.

Upcoming Topics

• The next lecture will cover the keywords ANY and ALL for conditional searches based on related data.

Key Terms & Definitions

• EXISTS — SQL keyword that checks if a subquery returns at least one record (TRUE or FALSE).
• NOT EXISTS — SQL keyword that checks if a subquery returns no records (TRUE or FALSE).
• Subquery (Nested Query) — A query within another SQL query, often used in conjunction with EXISTS.
• Frequent Flyer Table — A database table containing information on frequent flyer program members.

Action Items / Next Steps

• Review and practice writing SQL queries using EXISTS and NOT EXISTS.
• Prepare to learn about the ANY and ALL keywords in the next lecture.