Select with empty fields when you can not find

Question

Select with empty fields when you can not find

Navigation

#1 by (4 votes)

2

Assuming I have these two tables with the following records

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

tabla2
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

sql

asked by Takyo 12.05.2017 в 13:44

source

1 answer

Problems with merge and dplyr Default date in an input

score 4 · Accepted 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 = cliente.id

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.

Update

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 = cliente.id