CODEWITHSUNDEEP
mysqlsql
Mateusgf
Mateusgf

Reputation: 899

Query on mysql with inner join

I have 54567 rows in this jos_clientes_contratos table;

When I execute the query:

SELECT * FROM `jos_clientes_contratos` 
INNER JOIN jos_users 
ON jos_clientes_contratos.cpf_cnpj_cliente = jos_users.username;

It will return me 54560 rows. This means that there are 7 rows which were not listed on the results. How can I list these 7 rows from jos_clientes_contratos that have not this condition jos_clientes_contratos.cpf_cnpj_cliente = jos_users.username?

Upvotes: 1

Views: 57

Answers (3)

Peter
Peter

Reputation: 1348

Saying 'use left" is OK but why? In fact, when you use "INNER JOIN" , Mysql start to search all the data from your WHERE, then push them inside the "INNER JOIN" and get, as result, only the data from the WHERE which match also the INNER.

Exemple:

 John -> Kansas
 Michael -> California
 Mike -> California
 Moshe -> Jerusalem
 Mathew-> no data in he location part


 SELECT name, location
 FROM TAB_NAME
 INNER JOIN TAB_TOWN
 ON tname_town = tlocation
 WHERE tname_name = "M%"

I that case you will get 

 Michael -> California
 Mike -> California
 Moshe -> Jerusalem
 but NOT Mathew as Mathew don't have location.

If you want to have all people, even if they don't have location, you have to perform an OUTER JOIN.

 SELECT name, location
 FROM TAB_NAME
 LEFT OUTER JOIN TAB_TOWN
 ON tname_town = tlocation
 WHERE tname_name = "M%"

This will return

 Michael -> California
 Mike -> California
 Moshe -> Jerusalem
 Mathew-> undefined

Hope this help

Ah, just have a look here: simple, basic, good! http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

Upvotes: -1

xQbert
xQbert

Reputation: 35323

SELECT * 
FROM `jos_clientes_contratos` 
LEFT JOIN jos_users 
 ON jos_clientes_contratos.cpf_cnpj_cliente = jos_users.username
WHERE jos_users.username is null

Read up on Joins: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

LEFT Join basically says... Return all records from the left table (jos_clientes_contratos) and only those on the right (jos_users) which match. This is the nature of an outer join. LEFT, RIGHT, or full outer. They return all data from one or both of the sets including records that don't match.

Since you are interested in the jos_clientes_contratos records that do not match, you want the ones that have a jos_users_username that is null.

Upvotes: 2

Explosion Pills
Explosion Pills

Reputation: 191729

You can do an anti-join:

SELECT * FROM t1
LEFT JOIN t2 ON (t1.key = t2.key)
WHERE t2.key IS NULL

Upvotes: 4

Related Questions