Left join returning more records

Question

Left join returning more records

Navigation

#1 by (3 votes)
#2 by (1 votes)

1

I have a main table with about 5,000 records, and I need to fetch information from another table with 7,000 records.

But my query is returning +6000:

SELECT principal.id, info.nome, info.endereco, principal.valor
FROM principal
LEFT JOIN info
   ON info.usuario = principal.usuario

What can be happening?

sql sql-server

asked by anonymous 30.12.2014 / 15:02

2 answers

1

There may not be records in the info table for all records in the principal table, but the 5,000 records of this will still appear, because left join is actually left outer join .

There is no left inner join or right inner join , since inner join is always bidirectional, that is, there must be match in the two tables for the join condition.

The difference of a thousand rows probably occurs because a portion of the records of the principal table has two or more corresponding records in the info table.

30.12.2014 / 15:15

Simulate a Click event without clicking using jQuery OR returns result not expected

score 3 · Accepted Answer

LEFT JOIN , by definition, brings table rows even if they are null.

What is likely to be happening in your case is that not all rows in the principal table have corresponding rows in the info table. Also, some records in the principal table must have more than one row in the info table.