Does ‘Anti Join’ perform better than ‘Left Join’?

The answer is – YES..!!!

Anti-join is a very efficient SQL construct offered by Apache Impala. Anti-join between two tables returns rows from the first table where no matches are found in the second table. It is used to make the queries run faster. These joins are written using the LEFT ANTI JOIN constructs.

Let’s see how it works!

Assume there are two tables, employee and department. What would you do if you want to retrieve the names of the departments that has no employees associated to it? To retrieve this information, you will use LEFT OUTER JOIN (or LEFT JOIN), right?

Instead of using LEFT JOINS we can use ANTI JOINS for better performance. The following query returns the customers information who have no orders.

SELECT customer_id, customer_fname FROM Customers c
LEFT ANTI JOIN orders o ON c.customer_id = o.order_customer_id
ORDER BY customer_id
LIMIT 10;

(click on the image to enlarge)

The above query is similar to the below:

SELECT customer_id, customer_fname FROM customers c
LEFT JOIN orders o ON c.customer_id = o.order_customer_id
WHERE order_id IS NULL
ORDER BY customer_id
LIMIT 10;

(click on the image to enlarge)