Why does this query generate so much data?

Question

Err I apologize for the title, but I forgot what its actually called in SQL lingo.

Thing is using this query:

SELECT DISTINCT a.col1, a.col2,a.col3,c.col1, c.col2

FROM table1 a

LEFT JOIN table2 c ON a.col1 = c.col3

WHERE a.col2 = '038CQH'

I get a result with around 56000 rows, but when I checked using:

SELECT a.col1, a.col2,a.col3 FROM table1 a where a.col2='038CQH'

I get only 4 rows.

Answer 1

its the left join (left join is actually left outer join). It means - get all records from table1 and all the records from table2, but where a.col1 != c.col3, put nulls on all table2 columns.

Maybe an inner join is what you need? it would only add the columns from table2 where a.col1 = c.col3

Answer 2

It is because your 4 records from table1 join to many records in table2 based on the records in table2 where col3 matches col1 from table1. BTW - these are horrible column names.

Answer 3

A slightly different approach - re-run your query:

SELECT a.col1, a.col2,a.col3 FROM table1 a where a.col2='038CQH'

Note down the four values of a.col1 returned as [a1], [a2], [a3] and [a4].

Then try running the following query against table2:

SELECT DISTINCT c.col1, c.col2
table2 c WHERE c.col3 IN ([a1], [a2], [a3], [a4])

(where [a1] etc have been replaced with the previously noted values.)