Select with empty fields when you can not find


Assuming I have these two tables with the following records

id      nombre
1       c1
2       c2
3       c3
4       c4 
5       c5

id_cli   dato
1        d1
3        d3
10       d10

If this selection is made it shows us the clients that are in table2

SELECT tabla2.*, cliente.nombre 
FROM cliente,tabla2
WHERE id = id_cli

id_cli   dato    nombre
1         d1       c1
3         d3       c3

but what I'm looking for is to show me the next result

id_cli   dato    nombre
1         d1       c1
3         d3       c3
10        d10   

Let me see table2 and if the id matches the name field

Is this possible in sql? or I have to do it by code

asked by Takyo 12.05.2017 в 13:44

1 answer


I think what you are looking for is a LEFT JOIN , where the matching fields are shown, and the non matching of the left table. As explained in the MySQL manual .

SELECT tabla2.id_cli, tabla2.dato, cliente.nombre 
FROM tabla2 
LEFT JOIN cliente ON tabla2.id_cli =

It should work. Point out that the client fields that do not match will be shown as NULL .

Refer to this image to see how the different types of JOIN graphically work.  


It is possible to get the NULL fields to be shown blank, exactly as you wish. It's not particularly efficient, but it does what you want. The code is for MySQL . In MS SQL , the function IfNull is changed by IsNull , as explained in this question .

SELECT tabla2.id_cli, tabla2.dato, IfNull(cliente.nombre, '') as nombre 
FROM tabla2 
LEFT JOIN cliente ON tabla2.id_cli =
answered by 12.05.2017 / 13:53