CODEWITHSUNDEEP
phpmysqlsql
Sakrow
Sakrow

Reputation: 107

query for rows in table A that don't match rows in table B

I have two tables :

http://imageshack.us/f/854/7jpd.png/

I need "clientes.id_cliente, clientes.nombre, clientes.apellido, clientes.empresa, clientes.correo" from first table but deleting the persons that appears in the second table (people who contract)

I tried this (among many other things):

SELECT clientes.id_cliente, clientes.nombre, clientes.apellido, clientes.empresa, clientes.correo
FROM clientes
INNER JOIN contratos_clientes
ON clientes.id_cliente != contratos_clientes.id_cliente

and this is the result:

enter image description here

I have tried different ways, but I've gotten nothing.

Upvotes: 0

Views: 140

Answers (2)

yshavit
yshavit

Reputation: 43391

Conceptually, this is a NOT EXISTS or NOT IN -- but that may not optimize well. If the queries that @a_horse_with_no_name posted don't perform well, you can also do:

SELECT clientes.id_cliente, clientes.nombre, clientes.apellido,
        clientes.empresa, clientes.correo
FROM clientes
LEFT JOIN contratos_clientes
ON clientes.id_cliente = contratos_clientes.id_cliente
WHERE contratos_clientes.id_cliente IS NULL

A LEFT JOIN asks for all rows from the left table (clientes table here), joined to corresponding rows (as defined by ON) in the right table (contratos_clientes) if they exist. For each row on the left, if the values don't exist for a given row, then all of the values in the right table's part of the row will be NULL. In your case, those are the rows you want -- so just check if a value on that right side is NULL. It's best to use a value that can't be NULL on its own, such as a PK field.

Incidentally, this sort of query -- "rows in A that don't match rows in B" -- is called an anti-join.

(Note: Usually when you just want the left part of a JOIN, you need to use SELECT DISTINCT so that it'll remove duplicates if a given row on the left matches multiple rows on the right. But in this case, you're only looking for NULL rows on the right, and at most one such "dummy" row is inserted per row on the left, so you can't have duplicates and thus don't need the DISTINCT.)

Upvotes: 1

user330315
user330315

Reputation:

You want a NOT EXISTS query:

SELECT clientes.id_cliente, 
       clientes.nombre, 
       clientes.apellido, 
       clientes.empresa, 
       clientes.correo
FROM clientes
WHERE NOT EXISTS (select 1 
                  from contratos_clientes
                  where clientes.id_cliente = contratos_clientes.id_cliente);

Another option would be NOT IN:

SELECT clientes.id_cliente, 
       clientes.nombre, 
       clientes.apellido, 
       clientes.empresa, 
       clientes.correo
FROM clientes
WHERE clientes.id_cliente NOT IN (select contratos_clientes.id_cliente 
                                  from contratos_clientes
                                  where contratos_clientes.id_cliente IS NOT NULL);

The IS NOT NULL in the sub-query is necessary because the NOT IN operator will fail if a NULL value is returned for the contratos_clientes.id_cliente column. If that column is defined as NOT NULL that condition can be removed.

MySQL is known to be terribly bad at NOT IN statements, so the first one will probably perform better.

Upvotes: 1

Related Questions