Select union rest between tables

Asked

Viewed 121 times

1

I have two tables, customers and sales. I would like to know which customers have no sale, and store in the sales table the customer ID.

I tried it but to no avail:

SELECT *
FROM cliente
RIGHT JOIN vendas ON (vendas.id_cliente = cliente.id)
WHERE vendas.id_cliente IS NULL
mysql sql

3 answers

3


Use the clause NOT EXISTS 

SELECT * FROM cliente
WHERE NOT EXISTS(SELECT vendas.id_cliente FROM vendas WHERE vendas.id_cliente = cliente.id)

If the subquery no return records is because there is no such client id in the table sales, this makes that EXISTS be false, the NOT makes it true and that customer is returned by SELECT.

  • 2

    Exactly, the best way, so just in case he uses a pagination system he will have no trouble using the LIMIT offset, ...

0

Try with the LEFT JOIN, thus:

SELECT *
FROM cliente
LEFT JOIN vendas ON (vendas.id_cliente = cliente.id)
WHERE vendas.id_cliente IS NULL

  • Left Join brings the data tbm. More is not required. If he has any sale brings the same way.

  • I agree with you that LEFT JOIN will bring everything, but that’s why we use "WHERE sales.id_client IS NULL" will filter only those who have no sale. -1 therefore?

  • By logic we have no sales without customers, and we can have customers without sales. vo withdraw the -1

0

Hello, try this on:

SELECT *
FROM cliente
RIGHT JOIN vendas ON (vendas.id_cliente = cliente.id)
WHERE vendas.id_cliente = NULL
  • 4

    You can explain why this solution can solve the problem. So the PA (author of the question) will realize what was wrong and how to solve the problem...

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.