ORACLE | ||||||||
| ||||||||
| ||||||||
| ||||||||
| ||||||||
| ||||||||
| ||||||||
| ||||||||
|
Oracle - Anti Join
Anti-join is used to make the queries run faster. It is a very powerful SQL construct Oracle offers for faster queries.
Anti-join between two tables returns rows from the first table where no matches are found in the second table. It is opposite of a semi-join. An anti-join returns one copy of each row in the first table for which no match is found.
Anti-joins are written using the NOT EXISTS or NOT IN constructs.
Let's take two tables "departments" and "customer"
DEPARTMENT_ID | DEPARTMENT_NAME |
---|---|
1 | A |
2 | B |
3 | C |
4 | D |
CUSTOMER_ID | FIRST_NAME | LAST_NAME | DEPARTMENT_ID |
---|---|---|---|
1 | Niraksh | sarkar | - |
2 | Niroop | Paritala | - |
3 | Sweejya | Mehtha | - |
4 | Deepu | Nehra | 1 |
SELECT departments.department_id, departments.department_name
FROM departments
WHERE NOT EXISTS
(
SELECT 1
FROM customer
WHERE customer.department_id = departments.department_id
)
ORDER BY departments.department_id;
DEPARTMENT_ID | DEPARTMENT_NAME |
---|---|
2 | B |
3 | C |
4 | D |