Answer Posted / vivek
Anti-joins:
Anti-joins are written using the NOT EXISTS or NOT IN
constructs. An anti-join between two tables returns rows
from the first table for which there are no corresponding
rows in the second table. In other words, it returns rows
that fail to match the sub-query on the right side.
Suppose you want a list of departments with no employees.
You could write a query like this:
SELECT d.department_name
FROM departments d
MINUS
SELECT d.department_name
FROM departments d, employees e
WHERE d.department_id = e.department_id
ORDER BY department_name;
The above query will give the desired results, but it might
be clearer to write the query using an anti-join:
SELECT d.department_name
FROM departments d
WHERE NOT EXISTS (SELECT NULL
FROM employees e
WHERE e.department_id = d.department_id)
ORDER BY d.department_name;
| Is This Answer Correct ? | 17 Yes | 3 No |
Post New Answer View All Answers
What is an exception in pl/sql?
Why we use cross join?
How to call a javascript function from pl sql?
How do you create a db file?
Explain the difference between triggers and constraints?
how to delete an existing column in a table? : Sql dba
Are subqueries faster than joins?
How do you remove duplicates without using distinct in sql?
What are different types of statements supported by sql?
What is cross join example?
Is a secondary key the same as a foreign key?
Which function is used to return remainder in a division operator in sql?
How to make a copy values from one column to another in sql?
Mention what are different methods to trace the pl/sql code?
Does sql support programming?
