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 a data definition language?
Mention what problem one might face while writing log information to a data-base table in pl/sql?
What is a sql instance vs database?
What is trigger and types?
What are the different sql languages?
what are the advantages and disadvantages of cascading style sheets? : Sql dba
Is it important to partition hard disk?
What is a string data type in sql?
What is sharding in sql?
Explain the order of sql statement execution?
What are pl/sql packages?
Write a query to find the names of users that begin with "um" in sql?
What does pl sql developer do?
What is a recursive join sql?
What is the execution plan in sql?
