Inner Join and Outer Joins Producing the same Result

Question

I have 2 tables in Mysql one is holding contractors and another is holding Projects, I want to produce a contractor-Project Report showing the approtining of the projects. problem is INNER JOIN, LEFT and RIGHT OUTER JOINS, all produce the same result only showing the contractor with a project even when i leave out the condition which seems Weird. here are my statements

SELECT DISTINCT (tbl_contractor.name_v), count( tbl_project.name_v ) 
FROM tbl_contractor 
INNER JOIN tbl_project 
  ON tbl_project.Contractor=tbl_contractor.contractor_id_v 
  ON tbl_project.Contractor = tbl_contractor.contractor_id_v 
LIMIT 0 , 30;

SELECT DISTINCT (tbl_contractor.name_v), count( tbl_project.name_v ) 
FROM tbl_contractor
LEFT OUTER JOIN tbl_project 
ON tbl_project.Contractor = tbl_contractor.contractor_id_v 
LIMIT 0 , 30;

http://stackoverflow.com/questions/38549/sql-difference-between-inner-and-outer-join That link should help explain. Basically your inner join is taking intersecting fields while your outer join shows all fields on the left matching null values where they do not intersect. Assuming both tables match perfectly they would give the same data. — Tony318, May 21 '12 at 13:42
Suggestion: add a group by to your query to clarify what it is doing. — Gordon Linoff, May 21 '12 at 13:45

score 2 · Accepted Answer · answered May 21 '12 at 14:03

2

You have an aggregate function, COUNT(), without a GROUP BY. This means youir query will return one row only.

You probably need a GROUP BY (contractor):

SELECT tbl_contractor.name_v, COUNT( tbl_project.name_v ) 
FROM tbl_contractor
  LEFT OUTER JOIN tbl_project 
    ON tbl_project.Contractor = tbl_contractor.contractor_id_v 
GROUP BY tbl_contractor.contractor_id_v 
LIMIT 0 , 30;

answered May 21 '12 at 14:03

ypercubeᵀᴹ

113,259
19
174
235

You know, I've always misread the documentation so far: I've always read the documentation as implicitly adding a `GROUP BY` when none is provided. Instead, it is as you say: the original means, conceptually, `SELECT ANY(tbl_contractor.name_v), COUNT(tbl_project.name_v)` -- if MySQL had actually provided `ANY` as an aggregate function instead of doing this implicitly, it would be a lot less confusing. – May 21 '12 at 18:38
Yes, other DBMS will throw an error on such a query. MySQL blindly assumes that one know what one is doing and returns some answer (with random data in this case in column `tbl_contractor.name_v`) – ypercubeᵀᴹ May 21 '12 at 20:37

score 0 · Answer 2 · answered May 21 '12 at 13:42

0

By doing SELECT DISTINCT (tbl_contractor.name_v) the query will only return one row for each contractor name, try removing the distinct and see if you get a better contractor - project result.

answered May 21 '12 at 13:42

Jose Vega

10,128
7
40
57

score 0 · Answer 3 · answered May 21 '12 at 13:57

These queries are really group by queries on the contractor. If every contractor has at least one project, then the inner and left outer joins will return the same results. If there are contractors without projects, then the results are affected by the LIMIT clause. You are only getting the first 30, and, for whatever reason, the matches are appearing first.

Inner Join and Outer Joins Producing the same Result

3 Answers3