What are Anti joins



What are Anti joins..

Answer / 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

More SQL PLSQL Interview Questions

diff b/w function and procedure?

9 Answers   iFlex,


Difference between aggregate function and analytical function?

3 Answers   Metric Stream,


How do I make my sql query run faster?

0 Answers  


What does 0 mean in sql?

0 Answers  


What is auto increment feature in sql?

0 Answers  






Mention what is the function that is used to transfer a pl/sql table log to a database table?

0 Answers  


Suppose a student column has two columns, name and marks. How to get name and marks of the top three students.

0 Answers  


Explain alias in sql?

0 Answers  


What is sqlca in powerbuilder?

0 Answers  


Does normalization improve performance?

0 Answers  


Why stored procedures are faster than query?

0 Answers  


in a package one procedure is wrong package shows valid or not

4 Answers  


Categories