How to create Mysql search that returns lines without matching?

Question

I would like to know how I can do a search that returns records that are not part of the research condition.

For example, think about the situation where there is a database of a movie rental company with the table cliente and the table aluguel. The table cliente has a tuple cliente_id and the table aluguel has a tuple cliente_id.

What I want is to see the names of customers who have never rented a movie, IE, I wanted my query to return all names that do not satisfy the script below:

`select first_name, last_name from customer
inner join rental on customer.customer_id = rental.customer_id;`

Answer 1

Just use a LEFT JOIN, and check which relations return null:

SELECT nome, filme_id
   FROM cliente
   LEFT JOIN aluguel ON cliente.cliente_id = aluguel.cliente_id
   WHERE aluguel.cliente_id IS NULL;

See working on SQL Fiddle.

Explanation:

The LEFT JOIN causes all values in the left table to return, with or without a corresponding value in the right.

Thus the WHERE aluguel.cliente_id IS NULL causes the query return only cases where there is no film linked to that customer.

Understanding the types of join:

Worth a read on this question to know more about Joins:

What is the difference between INNER JOIN and OUTER JOIN?

A properly designed Join can prevent the use of Subqueries on a variety of occasions.

Answer 2

You can try using NOT IN()

SELECT first_name, last_name FROM customer WHERE NOT IN(
    select first_name, last_name from customer
    inner join rental on customer.customer_id = rental.customer_id
);

There is not much to explain because the name of the function is suggestive not in -> "who is not in..."

Answer 3

There are several ways you can do this, I will demonstrate 3 here but it is important to take into account that the use of subselect or subquery may impair performance so where possible choose to avoid them.

Using NOT EXISTS together with a SUBSELECT

SELECT first_name, last_name
FROM custumer
WHERE NOT EXISTS
   (
   SELECT NULL
   FROM rental
   WHERE rental.customer_id = custumer.customer_id
   LIMIT 1
)

See example working: SQLFIDDLE

Using NOT IN together with a SUBSELECT

SELECT first_name, last_name 
FROM custumer 
WHERE custumer.customer_id NOT IN
(
   SELECT rental.customer_id
   FROM rental
   WHERE rental.customer_id = custumer.customer_id
)

See example working SQLFIDDLE

Using LEFT JOIN together with the condition IS NULL

SELECT first_name, last_name 
FROM custumer
LEFT JOIN rental 
   ON custumer.customer_id = rental.customer_id
WHERE rental.customer_id IS NULL;

See example working: SQLFIDDLE