LEFT OUTER JOIN GIVING UNEXPECTED RESULT

Question

I'm making a simple query :

SELECT * FROM Bench LEFT JOIN  ASSIGNED_DOM 
        ON (Bench.B_id=ASSIGNED_DOM.B_id)  WHERE Bench.B_type=0 ;

As expected all the lines of Bench table are returned BUT If I try to get the B_id field I discovered that was put to NULL. Then I have tried with this other query that should be totally equivalent:

SELECT * FROM Bench LEFT JOIN  ASSIGNED_DOM USING (B_id)  WHERE Bench.B_type=0 ;

But in that case the B_id field is returned correctly. What's wrong with the first query? What the difference between the two ?

Please provide sample table data, with expected output, as well as what your current query is returning! — Madhur Bhaiya, Sep 16 '18 at 17:20
Imagine on table Bench(B_id, B_type): Row 1 : 1,0 Row 2 : 2,0 Row 3: 3, 1; and on table ASSIGNED_DOM(B_id,UPI): Row 1: 1,'3.4/xxxx/1.1' Row 2: NULL,'3.4/xxxx/1.2' What I want and obtain with with the Rows of Bench with B_type=0 and eventually the UPI value associated id there is match in B_id. BUT with the first query the B_id returned when there is NOT a match is NULL. — Giuseppe Levi, Sep 16 '18 at 18:10

Bart · Answer 1 · 2018-09-16T18:25:19.603

1

Values in second table overwrites values in the first one if column name is the same. Try to use an alias in your query

SELECT Bench.B_id AS bid1, ASSIGNED_DOM.B_id AS bid2 
FROM Bench 
LEFT JOIN  ASSIGNED_DOM ON (Bench.B_id=ASSIGNED_DOM.B_id) 
WHERE Bench.B_type=0;

edited Sep 16 '18 at 18:25

answered Sep 16 '18 at 18:19

Bart

1,268
2
12
14

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

The two queries are not equivalent. According to the documentation,

Natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard.

Redundant columns of a NATURAL join do not appear.

That specifically comes down to the following difference:

A USING clause can be rewritten as an ON clause that compares corresponding columns. However, although USING and ON are similar, they are not quite the same.

With respect to determining which rows satisfy the join condition, both joins are semantically identical.

With respect to determining which columns to display for SELECT * expansion, the two joins are not semantically identical. The USING join selects the coalesced value of corresponding columns, whereas the ON join selects all columns from all tables.

So your first query has two columns of the same name, bench.B_id, ASSIGNED_DOM.B_id, while the second one just has one, coalesce(bench.B_id, ASSIGNED_DOM.B_id) as B_id.

It will depend on your application/framework how exactly the first case will be handled. E.g. the MySQL client or phpmyadmin will just display all columns. Some frameworks may alter the names in some way to make them unique.

php in particular (and I assume you are using this) will not though: if you use $row['B_id'], it will return the last occurance (although that behaviour is not specified), so in your case you will get ASSIGNED_DOM.B_id. You can however still access both columns with their index (e.g. $row[0], $row[1]), but just one of those with their identical column name.

To prevent such problems, you can/should use aliases, e.g. select bench.B_id as bench_B_id, ASSIGNED_DOM.B_id as ASSIGNED_DOM_B_id, ....

Nice and clear answer. I think that really completes what is written at: https://stackoverflow.com/questions/11366006/mysql-on-vs-using . In fact I'm using php and now I understand the origin of the problem. — Giuseppe Levi, Sep 17 '18 at 14:57

LEFT OUTER JOIN GIVING UNEXPECTED RESULT

2 Answers2