MSK Institute • Shikohabad
Loading Page Content...
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
Connecting serverPlease wait...
💻 Practical Labs🚀 Free Demo📜 Verified Certificates
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
When querying relationships between tables, your business logic often requires testing for existence rather than retrieving actual data values. For example: "Find all customers who have placed at least one order" or "Find all products that have never been reviewed". In SQL, these existence tests are implemented using EXISTS (Semi-Join) and NOT EXISTS (Anti-Join).
EXISTS Operator (Semi-Join)The EXISTS operator evaluates a subquery and returns TRUE as soon as the subquery produces at least one row. If the subquery produces zero rows, it returns FALSE.
SELECT 1?Because EXISTS only checks whether a row exists, it does not care what columns are projected in the subquery. By convention, developers write SELECT 1 (or SELECT *). The database query engine ignores the projection entirely and performs an Early Termination Index Seek!
EXISTS SpeedConsider a customer who has placed 10,000 orders over 5 years:
INNER JOIN will match and process all 10,000 order records, expanding the result set and requiring a DISTINCT pass to remove duplicates.EXISTS subquery inspects the index, finds the very first matching order, stops scanning immediately, and returns TRUE!NOT EXISTS Operator (Anti-Join)The NOT EXISTS operator returns TRUE if the subquery returns zero rows:
EXISTS vs IN: Which is Better?| Feature | EXISTS | IN |
|---|---|---|
| Execution Style | Boolean existence test (Early termination). | Evaluates values against an extracted set. |
| Performance with Large Subquery | Faster (Stops on first match). | Slower if subquery result set is large. |
| Handling of NULLs | Safe (Does not fail on NULLs). | Dangerous with NOT IN (Returns 0 rows if list has a NULL!). |
| Readability | Requires correlated link. | Clean and intuitive for static lists. |
In database internals:
EXISTS): Returns rows from the first table if they match the second table, without duplicating rows from the first table if multiple matches exist.NOT EXISTS): Returns rows from the first table that have no matching records in the second table.NOT IN fails completely when subqueries contain NULL values. NOT EXISTS is completely immune to this problem and should be your default choice.orders.customer_id) to guarantee $O(log N)$ seeks for each outer check.EXISTS operator check for in a subquery?SELECT 1 inside an EXISTS subquery?EXISTS have over an INNER JOIN ... DISTINCT query?NOT EXISTS preferred over NOT IN when querying nullable foreign key relationships?Continue learning with hands-on practice, examples, and exercises in the upcoming topic.
| Previous Lesson | Next Lesson |
|---|---|
| Correlated Subqueries | Simple CASE Expression |
Test your understanding of this lesson with 5 questions. Each question has one correct answer.